sql oracle update table data

Question

I got two tables: table1 and table2

table1:

HW_SN (varchar2)         ID (number)

123ERZ123                (empty)
124NIJD11
4125ERT22
....

-

table2:

ID (number)             Name (varchar)

85442                   123ERZ123
85471                   124NIJD11
12478                   4125ERT22
...                     ...

table1 has another column called ID which is empty.

I need to check table1-HW_SN if its the same to table2-Name - if yes it should add table2-ID to table1-ID.

http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join — Abhik Chakraborty, Apr 21 '15 at 11:07

score 1 · Accepted Answer · answered Apr 21 '15 at 11:10

1

UPDATE table1
SET ID = (SELECT table2.ID
                     FROM table2
                     WHERE table1.HW_SN = table2.Name)
WHERE EXISTS (SELECT table2.ID
                     FROM table2
                     WHERE table1.HW_SN = table2.Name);

answered Apr 21 '15 at 11:10

Matt

14,906
27
99
149

Thanks Matt - my mistake was I totally forgot about the 'WHERE EXISTS' part - helped me out! – piguy Apr 21 '15 at 11:12

score 0 · Answer 2 · answered Apr 21 '15 at 11:10

This is an update operation. Unfortunately, Oracle does not support join with update, so you have two choices. You can use merge. Or, in this case, a subqueries accomplish what you want:

update table1
    set id = (select id from table2 t2 where t2.name = table1.hw_sn and rownum = 1)
    where exists (select 1 from table2 t2 where t2.name = table1.hw_sn);

score 0 · Answer 3 · answered Apr 21 '15 at 11:11

0

UPDATE table1 t1
SET id = (SELECT id FROM table2 t2 WHERE t2.name = t1.hw_sn)

that's your homework done for you!

answered Apr 21 '15 at 11:11

davegreen100

2,055
3
13
24

sql oracle update table data

3 Answers3