XPost: comp.database.oracle, comp.databases.ms-sqlserver, micros   
   ft.public.sqlserver   
   From: srielau@ca.ibm.com   
      
   Hugo Kornelis wrote:   
   > On Fri, 26 Nov 2004 11:01:39 +0100, Jan van Veldhuizen wrote:   
   >   
   >   
   >>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.   
   >   
   >   
   > Hi Jan,   
   >   
   > That's right. Using ANSI-standard SQL, the only way to update multiple   
   > columns with values from another table is to repeat the subquery:   
   >   
   > UPDATE Table1   
   > SET    city_id = (SELECT T2.id FROM etcetera...)   
   >      , another_column = (SELECT other_column FROM etcetera...)   
   > WHERE  ....   
   >   
   > Best, Hugo   
   I believe the ANSI standard allows:   
   UPDATE Table1   
      SET (city_id, another_column) = (SELECT T2.id, other column FROM etc   
   WHERE ...)   
   WHERE EXISTS(...)   
      
   Cheers   
   Serge   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)