XPost: comp.database.oracle, comp.databases.ms-sqlserver, micros   
   ft.public.sqlserver   
   From: jan@van-veldhuizen.nl   
      
   Thanks. I'm going to test that.   
      
   That syntax will work with one column to be updated.   
   What if I have to columns?   
      
   I think the oracle sql will support something like:   
   UPDATE Table1   
     SET (city_id, another_column) =   
     (SELECT T2.id, other_column FROM etctera...   
      
   But that no standard SqlServer syntax as far as I know.   
      
   "David Portas"  wrote in message   
   news:qoednbGWLZwC-TvcRVn-1A@giganews.com...   
   > 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   
   > --   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)