Oracle update and change few columns records in two tables with a single query

I have two tables with names of Locations and Countries in My Oracle database. Locations table has columns with names location_id(fk), street_address, state_province, and country_id. Countries table has columns with names country_name, country_id, location_id(fk). I wanna update some columns of Locations and Countries table in a single query. I did it with the below query but it doesn’t work.

update (select l.street_address, l.postal_code, l.city, l.state_province, c.country_name 
 from hr.countries c, hr.locations l where l.country_id = c.country_id ) 
 set l.street_address = '147 Spadina Ave',
       l.postal_code = '122000215',
       l.city = 'Toronto',
       l.state_province = 'Ontario',
       c.country_name = 'US'
 where l.location_id = 1200;

but I faced with the error from PL/SQL (ORA-00911 : invalid character).

please help me to fix this issue.

Answer

That error, ORA-00911 : invalid character has nothing to do with your update statement and may be coming from another part of your PL/SQL ?

What’s actually wrong with your update statement is that you cannot update more than one table through an inline view. So, If you remove the alias l in the set clause and run the statement, you should get ORA-01776:cannot modify more than one base table through a join view

You may however be able to do it using an INSTEAD OF TRIGGER with a view.

In your case, since you are updating the tables based on a given primary key (location_id = 1200), running these two simple updates should be fine. There’s no need of joins

UPDATE hr.locations
SET street_address = '147 Spadina Ave',
    postal_code = '122000215',
    city = 'Toronto',
    state_province = 'Ontario'
WHERE location_id = 1200;


UPDATE hr.countries
SET country_name = 'US' -- Toronto in United states, how did that happen?
WHERE country_id IN ( SELECT country_id
                       FROM hr.locations
                       WHERE location_id = 1200
                     );