CODEWITHSUNDEEP
sqldb2
Robert Pitrone
Robert Pitrone

Reputation: 119

Update table column based on another table based on ID

I have 2 tables 'table1' and 'table2'. table1 has 10,000 records and table2 has 5,000 records. Both tables have "RECORD_ID" column. All RECORD_ID's that are in table2 can be found in table1.

I want to UPDATE the "PRICE" column of table1 based on the "RECORD_ID" column of table2. 

update  table1 set PRICE = table2.PRICE where RECORD_ID = table2.RECORD_ID

I got this error message:

SQL0206N "table2.PRICE" is not valid in the context where it is used SQLSTATE=42703

I am using DB2.

Upvotes: 1

Views: 77

Answers (3)

Esperento57
Esperento57

Reputation: 17462

Try this:

UPDATE table1 f1 
SET f1.price =(
                SELECT f2.price 
                FROM table2 f2 
                WHERE f2.record_id = f1.record_id
              )
WHERE exists
(
   SELECT f2.price 
   FROM table2 f2 
   WHERE f2.record_id = f1.record_id
)

Upvotes: 1

DKyleo
DKyleo

Reputation: 826

UPDATE table1 SET table1.price = (SELECT table2.price FROM table2 WHERE table2.record_id = table1.record_id)

Upvotes: 2

Raviraj Palvankar
Raviraj Palvankar

Reputation: 879

You have to use a join like this:

UPDATE
Table1
SET
Table1.Column = T2.Column
FROM
Table1 T1
INNER JOIN
Table2 T2
ON 
T1.PK = T2.FK;

Upvotes: 1

Related Questions