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.

without showing what you tried?

aviad
– aviad

2015-04-21 11:06:26 +00:00
Commented Apr 21, 2015 at 11:06 — aviad
– aviad, Commented Apr 21, 2015 at 11:06
stackoverflow.com/questions/2446764/…

Abhik Chakraborty
– Abhik Chakraborty

2015-04-21 11:07:33 +00:00
Commented Apr 21, 2015 at 11:07 — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 21, 2015 at 11:07

Matt · Accepted Answer · 2015-04-21 11:10:19Z

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, 2015 at 11:10

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

piguy Over a year ago

Thanks Matt - my mistake was I totally forgot about the 'WHERE EXISTS' part - helped me out!

Gordon Linoff · Accepted Answer · 2015-04-21 11:10:15Z

0

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

answered Apr 21, 2015 at 11:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

davegreen100 · Accepted Answer · 2015-04-21 11:11:01Z

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, 2015 at 11:11

davegreen100

2,1253 gold badges15 silver badges25 bronze badges

Collectives™ on Stack Overflow

sql oracle update table data

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related