CODEWITHSUNDEEP
sqloracle-databaseoracle11g
JavaSheriff
JavaSheriff

Reputation: 7665

Oracle Sql update multiple records at once?

Is it possible to Update multiple records in one sql query?
so update the status of all printers with id A and ip 1.1.1.1 to working (true), and all other as not working (false)
So basically combining these two query together into one query:

update printers set status = true ,row_update_date =sysdate where printer id = 'A' and printer ip = '1.1.1.1'
update printers set status = false ,row_update_date =sysdate where printer id = 'A' and printer ip != '1.1.1.1'

table structure: 

printers table:

printer ID,printer ip, status,row_update_date 
A         ,1.1.1.1   ,
A         ,1.1.1.2   ,
A         ,1.1.1.3   ,
A         ,1.1.1.4   ,
B         ,1.1.2.1   ,
B         ,1.1.2.2   ,

UPDATE
I forgot row_update_date !

Upvotes: 1

Views: 723

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Alternatively, you can use decode for Oracle's SQL for switch-case type statements

update printers 
   set status = decode(printer_IP,'1.1.1.1','true','false'),
       row_update_date = sysdate
 where printer_ID = 'A';

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

You could use CASE:

update printers 
set status = CASE WHEN printer ip = '1.1.1.1' THEN true ELSE false END
    ,row_update_date = SYSDATE
where printer id = 'A'

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to UPDATE with CASE WHEN

update printers 
set status = (CASE WHEN  printer ip = '1.1.1.1' 
                        THEN true
                   WHEN  printer ip != '1.1.1.1' 
                        THEN false 
              END)
WHERE printer id = 'A'

Upvotes: 1

Related Questions