Home » SQL & PL/SQL » SQL & PL/SQL » multiple update statements
multiple update statements [message #232887] Mon, 23 April 2007 16:35 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I have various update statements in a SQL scrit, as follows:-

UPDATE employee_operation_schd
SET end_hours=16
WHERE center='LON' AND day_of_week='FRIDAY';

UPDATE employee_operation_schd
SET end_hours=16
WHERE center='LON' AND day_of_week='MONDAY';

UPDATE employee_operation_schd
SET end_hours=18
WHERE center='NYC' AND day_of_week='MONDAY';

is there a way I can put them in one statement?
Re: multiple update statements [message #232932 is a reply to message #232887] Tue, 24 April 2007 02:36 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes, but I don't think it's very practical.

You could use case in the update, like :

CREATE TABLE test (
   col1 VARCHAR2   (5),
   col2 VARCHAR2   (5),
   col3 VARCHAR2   (5)
);


INSERT INTO test VALUES ('A','B','');
INSERT INTO test VALUES ('C','D','');
INSERT INTO test VALUES ('E','F','');


UPDATE test
SET col3 = CASE
              WHEN col1 = 'A' AND col2 = 'B' THEN 'X'
              WHEN col1 = 'C' AND col2 = 'D' THEN 'Y'
              WHEN col1 = 'E' AND col2 = 'F' THEN 'Z'
          END
WHERE (col1 = 'A' AND col2 = 'B')
   OR (col1 = 'C' AND col2 = 'D')
   OR (col1 = 'E' AND col2 = 'F');

SELECT * FROM test;


If you know you will have to update ALL rows anyway, you could leave the where part out of the update.
Previous Topic: Max sal
Next Topic: number datatype
Goto Forum:
  


Current Time: Sat Dec 03 12:16:30 CST 2016

Total time taken to generate the page: 0.09201 seconds