If I had two tables like:
first second
a | b | c b | d
---------- -----
1 | 2 | 2 | 4
2 | 3 | 3 | 5
and I wanted to update table first so that it writes column c to be column a * column d, how would I do this in a single query?
I'd want something like these two statements combined:
SELECT * FROM first LEFT OUTER JOIN second on first.b=second.b
and
UPDATE first SET c = (b * d)
TRY THIS: I am not sure about syntax of SQLLITE but you can use one of the following with INNER JOIN
--MYSQL
UPDATE test1 t1
INNER JOIN test2 t2 ON t1.b = t2.b
SET t1.c = t1.a*t2.d
--SQL-SERVER
UPDATE t1 SET t1.c = t1.a*t2.d
FROM test1 t1
INNER JOIN test2 t2 ON t1.b = t2.b
It Should work for SQLLITE
UPDATE test1 SET test1.c = test1.a *
(SELECT d FROM test2 WHERE test2.b = test1.b)
Try this:
UPDATE
first
SET
first.c = second.d
FROM
first INNER JOIN second ON first.d= second.d
This might be of some use (for oracle):
update first t1
set t1.c =
( Select t1.b*t2.d
from second t2
where t1.b = t2.b);
