How to run an SQL UPDATE query where a field is modified and then used

Question

Is the following posible on SQL? (Using SQLITE3)

UPDATE Table SET
Value1 = (SELECT ...)
Value2 = Value3/Value1; # The new value1!!! Not the value currently on the DB

Right now I need to do something like:

UPDATE Table SET
Value1 = (SELECT ...)
Value2 = Value3/(SELECT ...);

This makes the code quite long. Imagine it is not only Value1 I am updating but other fields too.

So the only solution I found is to do 2 UPDATES. First:

UPDATE Table SET
Value1 = (SELECT ...);

And then

UPDATE Table SET
Value2 = Value3/Value1;

Anyone with a more "beautiful" way of doing this?

Thanks,

I would consider doing the inner `SELECT` first, then `UPDATE` the two Values in one statement. — Bob Kaufman, Oct 14 '15 at 15:24

score 1 · Answer 1 · answered Oct 14 '15 at 16:34

1

In SQLite 3.8.3 or later, you can use a common table expression:

WITH u(v) AS (
    SELECT ...
)
UPDATE MyTable
SET Value1 = (SELECT v FROM u),
    Value2 = Value3 / (SELECT v FROM u);

(This even works with correlated subqueries.)

answered Oct 14 '15 at 16:34

CL.

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

Maybe you can try replace

replace into table2
(Value1 , Value2)
select src.Value1 , src.Value3 / src.Value1
from table1 src

In sql you cant do UDPATE ... FROM as explain in this answer

edited May 23 '17 at 12:15

Community

answered Oct 14 '15 at 15:24

This will delete any old rows, so the values in the other columns will be lost. – CL. Oct 14 '15 at 16:26

score -1 · Answer 3 · answered Oct 14 '15 at 15:32

-1

You can do like this :

update Table1 set szNo = KO.nNo 
from 
(select nNo From Table2) KO

answered Oct 14 '15 at 15:32

Viandry

I dont think you can do that in sqllite – Juan Carlos Oropeza Oct 14 '15 at 16:28

3 Answers3