CODEWITHSUNDEEP
sqloracle-databasesql-updateoracle-sqldeveloper
lipdjo
lipdjo

Reputation: 161

Update multiple rows with data from another table

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

Upvotes: 1

Views: 4929

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

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.

Upvotes: 2

Dr Y Wit
Dr Y Wit

Reputation: 2020

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".

Upvotes: 0

Related Questions