Re: Update statement syntax?

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Sat, 20 Feb 1999 12:48:41 +0100
Message-ID: <36cea11a$0$3421@pascal>

HoMan wrote
>The UPDATE statement in Transact-SQL can take the form:
>UPDATE tableA
> Set col = <expression>, ...
> FROM TableB, TableC
> WHERE <condition> ;

update tableA a
set ( a.col1, a.col2 ) =

    ( select b.col3, c.col4

      from tableB b, tableC c
      where b.id1 = a.id1
      and b.id2 = c.id2

    )
, a.col3 =

    ( select d.col1

      from tableD d
      where d.id1 = a.id2

    )
where a.col3 <=

    ( select max( e.col1 )
      from tableE e
    )
and ( a.col4, a.col5 ) =

    ( select f1.col1, f1.col2

      from tableF f1
      where f1.id1 = a.id3

    )
and a.col6 in

    ( select distinct f2.col3

      from tableF f2
      where f2.id2 = a.id3

    );

Get the picture? :-)

The only thing you cannot do is update two tables in one statement, like

update tableA a, tableB b
set a.col1 = ..., b.col1 = ...
where a.col3 = b.col4;

Arjan. Received on Sat Feb 20 1999 - 05:48:41 CST