update query on multiple tables

Question

I have a schema like :

• employees (eno, ename, zip, hdate)
• customers (cno, cnmae, street, zip, phone)
• zipcodes (zip, city)

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?

Answer 1

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.

Answer 2

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.

Answer 3

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
   

Answer 4

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

Answer 5

Hi,

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; /

Answer 6

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.