Cici Cici - 6 months ago 12
SQL Question

update one table according to data from another table

I have 2 tables,

table1
with cols
v1
and
v2
,
table2
with cols
v3
and
v4
.

I want to update
table2.v2
if
table1.v1
have a matching value in
table2.v3
.

I have the following code that seems to work

UPDATE table1
SET v2 = 99
WHERE exists(select v3 from table2
where table2.v3=table1.v1);


I also tried with an intersect version which failed (updates both rows of v2)

UPDATE table1
SET v2 = 99
WHERE exists(select v1 from table1
intersect select v3 from table2);

Answer

I don't think you can use intersect in this query because you have not used any joining condition between the sub query and outside query. I do think Exists expects that kind of condition.

Try using this,

UPDATE table1
SET v2 = 99
WHERE v1 in (select v1 from table1
intersect select v3 from table2);
Comments