oracle bulk update

Question

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?

Answer 1

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