0

Is there any solutions to make bulk update for oracle? After researching didn't found any related topic to my question but let's see, having following query

UPDATE USERS SET(NAME) = CASE
WHEN ID = (1, 2) THEN('NAME1', 'NAME2') end where ID IN (1, 2);

Having the following exception - ORA-01797: this operator must be followed by ANY or ALL

What I'm doing wrong here?

5
  • 1
    "What I'm doing wrong here?" -- you are violating the CASE expression syntax rules.
    – mustaccio
    Oct 14 '21 at 13:23
  • Understood, is there any other approaches that I could deal with bulk update? I would like not to use update approach with temporary tables or creating multiple case - then statements for each Id, is at allowed somehow?
    – semper fi
    Oct 14 '21 at 13:32
  • 1
    Your use of "bulk update" is unfamiliar to me. Can you explain, using some sample data, what exactly you are trying to achieve? Please consider following these suggestions.
    – mustaccio
    Oct 14 '21 at 13:47
  • Are you "bulk updating" only two rows? Or a random number of rows? Oct 14 '21 at 14:20
  • @MichaelKutz It would update a random number of rows
    – semper fi
    Oct 14 '21 at 21:15
2

You have a syntax error with your case expression, I would guess you want

UPDATE USERS 
SET NAME = CASE ID WHEN 1 THEN 'NAME1'
  WHEN 2 THEN 'NAME2' end 
where ID IN (1, 2);

If you want to handle a large amount of different keys and values then you could use one statement with bulk binds (array binds), the coding of this will depend on the driver you are using but it will look something like

statement='update users set name = :name where id = :id';
binds = array of names, array of ids;
execute(statement, binds);

In general, this will perform similarly to the previous approach. The previous approach is favourable if this is a one off.

You could also load a staging table with id,name pairs from a flat file with SQL*Loader and then use a merge statement

merge into users u
using staging_users su
on (u.id = su.id)
when matched then update
set u.name = su.name
6
  • Well, I know I could go this way, but instead I was trying to ask if there a possible way to pass an array of ids = values instead of making multiple copy of when -> then
    – semper fi
    Oct 14 '21 at 22:40
  • 1
    Have you tried decode? That syntax is a bit shorter: set name = decode(id,1,'NAME1',2,'NAME2') Oct 15 '21 at 6:33
  • @semperfi That still wouldn't be a "bulk update", though. It seems you are asking for a way to pass array arguments to a case expression. That isn't part of its syntax, so you can't. You could write a function to do that, though. Oct 19 '21 at 22:38
  • I've added some alternative ways of doing this that you might consider more bulk update Oct 20 '21 at 6:17
  • @AndrewSayer a MERGEis not necessary here, an UPDATE statement on a query will work: sqlfiddle.com/#!4/59898/1
    – miracle173
    Oct 20 '21 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.