CODEWITHSUNDEEP
sqloracle-databasesql-update
jon
jon

Reputation: 821

update query on multiple tables

I have a schema like :

where zip is pk in zipcodes and fk in other tables.
I have to write an update query which updates all the occurence of zipcode 4994 to 1234 throughout the database. 

update zipcodes,customers,employees  
  set zip = 0  
where customers.zip = zipcodes.zip 
  and employees.zip = zipcodes.zip;

but i know i am not doing it right. Is there a way to update all the tables zip ina single update query?

Upvotes: 3

Views: 4413

Answers (6)

Nanda Kishore
Nanda Kishore

Reputation: 1

It may sloves your problem. And you can update or delete in for loop basend on the values

Begin
for Zip_value in 1234.. 4559 loop
update zipcodes
  set zip = 0   
where customers.zip = zipcodes.zip  
  and employees.zip = zipcodes.zip;
  and zipcodes.zip = Zip_value;
update customers
  set zip = 0   
where customers.zip = zipcodes.zip  
  and employees.zip = zipcodes.zip;
  and zipcodes.zip = Zip_value;
update employees   
  set zip = 0   
where customers.zip = zipcodes.zip  
  and employees.zip = zipcodes.zip;
  and zipcodes.zip = Zip_value;
  END LOOP;
commit;
end;
/

Upvotes: 0

ondra
ondra

Reputation: 9331

I didn't test it - but: the SQL language allows updatable views. If you look here, you will see that Oracle indeed supports inherently updatable views. This way you could probably update more tables with one statement. There are some restrictions described here on joins, but at least something should be possible.

However; this is clearly not applicable to your situation, multiple statements would work much better. What you might consider is "on update cascade", this way you would update the table with the cities with new zipcode and the customers and employees would get updated automatically.

Update: on update cascade doesn't work in Oracle :( So triggers or multiple statements.

Upvotes: 1

Dustin Laine
Dustin Laine

Reputation: 38503

You cannot update multiple tables in one update statement. You will need to write multiple updates statement.

NEW THOUGHT: You could add triggers to update down the line, that way your app could update 1 table and triggers would propogate the rest.

Upvotes: 2

Pavunkumar
Pavunkumar

Reputation: 5335

Use stored Procedure . There you can do this multiple table operation using conditional statements.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

Oracle doesn't support multi-table update statements.

  1. Confirm that zipcode "1234" exists in the ZIPCODES table

    INSERT INTO ZIPCODES 
  (zip, city)
VALUES
  (1234, '?')

  2. Write separate update statements for the CUSTOMERS and EMPLOYEES tables:

    UPDATE CUSTOMERS
   SET zip = 1234
 WHERE zip = 4994

UPDATE EMPLOYEES
   SET zip = 1234
 WHERE zip = 4994

  3. Delete the previous code:

    DELETE FROM ZIPCODES
 WHERE zip = 4994

Upvotes: 3

Kangkan
Kangkan

Reputation: 15571

I think, you should insert a new row for the new zip in the zipcode table first, update the zip in the rest of the tables then and finally delete the old zipcode.

Upvotes: 1

Related Questions