CODEWITHSUNDEEP
sqlsql-update
Cici
Cici

Reputation: 1447

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);

Upvotes: 1

Views: 2458

Answers (5)

Mahesh Wagh
Mahesh Wagh

Reputation: 161

As per my point of view first check the value of table by 'If Exists' as below and than applied to update.

IF EXISTS
(
 select V1 from T2
 where v1 in (select v1 from t2)
)

BEGIN
UPDATE T1
SET v2 = 99
end

Cheers.....

Upvotes: 2

Shankar Kamble
Shankar Kamble

Reputation: 3023

For this we have two Answers

1)With Inner Join

UPDATE table1
SET v2 = 99
FROM table2 
     INNER JOIN table1
     ON table2.v3= table1.v1

2) With in clause

 UPDATE table1
 SET v2 = 99
 Where v1 in (Select v3 FROM table2)

Upvotes: 4

Santhosh
Santhosh

Reputation: 1791

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);

Upvotes: 1

Ken Clark
Ken Clark

Reputation: 2530

Use join to update table.

UPDATE table2
SET v2 = 99
From table2 Inner Join table1 on  table1.v1=table2.v3

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please check whether the following is your requirement:

UPDATE table1
SET v2 = 99
WHERE v1 IN (select v3 from table2)

Upvotes: 1

Related Questions