CODEWITHSUNDEEP
sqloracle-database
Sam Wolf
Sam Wolf

Reputation: 13

Multiple Update Statement in Oracle

Can we perform multiple Update Statement in SQL Server

. I have tried with but SQL Server error is occurred.

Error

ORA-00907: missing right parenthesis

The example is 

UPDATE FACULTY SET Salary=(
12000 WHERE Faculty_ID=0163,
8,500 WHERE Faculty_ID=0164,
14,500 WHERE Faculty_ID=0165,
10,000 WHERE Faculty_ID=0166
);

Upvotes: 0

Views: 11032

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I would suggest just writing four update statements:

UPDATE FACULTY SET Salary = 12000 WHERE Faculty_ID = 0163;
UPDATE FACULTY SET Salary = 8500 WHERE Faculty_ID = 0164;
UPDATE FACULTY SET Salary = 14500 WHERE Faculty_ID = 0165;
UPDATE FACULTY SET Salary = 10000 WHERE Faculty_ID = 0166;

Although you can do this in one statement, the logic is more complicated, prone to error, and harder to maintain. With an index on Faculty_ID the overhead for four queries should be pretty small.

Note about numbers and strings. SQL does not recognize commas as a separator within a number, so don't use that. Also, the leading zero in the id is unnecessary for a number. If Faculty_ID is a string, then use single quotes.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Use CASE statement's

UPDATE FACULTY
SET    Salary = CASE
                  WHEN Faculty_ID = '0163' THEN 12000
                  WHEN Faculty_ID = '0164' THEN 8500
                  WHEN Faculty_ID = '0165' THEN 14500
                  WHEN Faculty_ID = '0166' THEN 10000
                END
WHERE  Faculty_ID IN ( '0163', '0164', '0165', '0166' )

Considering you don't want to update other Faculty_ID's

Upvotes: 5

Related Questions