CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-updatetoad
Rasinger
Rasinger

Reputation: 9

SQL UPDATE within BEGIN and END

I need to update big table (ACCOUNTS) and change column based on itself.

How do do this?

This query:

begin
UPDATE ACCOUNTS SET ACC = '1' where ACC IN ('3');
UPDATE ACCOUNTS SET ACC = '2' where ACC IN ('4');
end

Updates only 4 to 2.

Upvotes: 0

Views: 6267

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

For larger tables it is anyway better to perform only one update.

You may combine both updates in one as follows:

select * from ACCOUNTS;

A
-
1
2
3
4


BEGIN
  update ACCOUNTS
  set ACC = case when ACC = '3' then '1'
                 when ACC = '4' then '2' end
  where ACC in ('3','4'); 
  dbms_output.put_line('rows updated '  || SQL%ROWCOUNT);
END;
/

rows updated 2

select * from ACCOUNTS;

A
-
1
2
1
2

Do not forget to COMMIT.

Upvotes: 4

Related Questions