Update multiple rows with data from another table

Question

I have to update 700 rows on a table. Is it possible to do it with only one query?

2 examples:

UPDATE PERSON p SET p.admin = (select usr.iqid from USER usr where usr.userid = 'J072') where upper(person.myid) = '18349';

UPDATE PERSON p SET p.admin = (select usr.iqid from USER usr where usr.userid = 'PU96') where upper(person.myid) = '36895';

Possible duplicate of [Update statement with inner join on Oracle](https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle) — Esteban P., Sep 22 '17 at 08:12
Please provide more details , Is there a way to join the two tables ? Can you specify the DDL of two tables with the joining logic ? — Jayesh Mulwani, Sep 22 '17 at 08:14
sorry..Yes it's possible to join the 2 tables person.admin=user.iqid — lipdjo, Sep 22 '17 at 08:41

score 1 · Accepted Answer · answered Sep 22 '17 at 10:53

I would write this as:

UPDATE PERSON p
    SET p.admin = (SELECT u.iqid 
                   FROM USER u
                   WHERE (u.userid = 'J072' AND p.myid = '18349') OR
                         (u.userid = 'PU96' AND p.myid = '36895')
                  )
    WHERE p.myid IN ('18349', '36895');

Notes that upper() is not needed for numbers. It would generally impede the use of indexes.

score 0 · Answer 2 · answered Sep 22 '17 at 09:04

Option 1.

update person p
   set p.admin      =
          (select usr.iqid
             from user usr
            where usr.userid = decode(upper(person.myid),  '36895', 'PU96',  '18349', 'J072'))
 where upper(person.myid) = any ('36895', '18349');

Option 2. Use merge statement to avoid correlated scalar subquery.

Option 3. Update (select ... from person join user) set ... if person has foreign key references user. Google "key preserned view".

Update multiple rows with data from another table

2 Answers2