Re: common UPDATE syntax for SqlServer and Oracle

The ANSI Standard syntax supported by both products is

UPDATE Table1
 SET city_id =
(SELECT T2.id

   FROM Table2 AS T2
   WHERE T2.city = Table1.city) ;

Depending on requirements you may want to include a WHERE EXISTS (equivalent to the proprietary INNER JOIN syntax)

UPDATE Table1
 SET city_id =
(SELECT T2.id

   FROM Table2 AS T2
   WHERE T2.city = Table1.city)
 WHERE EXISTS
(SELECT *

   FROM Table2 AS T2
   WHERE T2.city = Table1.city) ;

-- 
David Portas
SQL Server MVP
--