Re: common UPDATE syntax for SqlServer and Oracle
From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Thu, 25 Nov 2004 23:02:53 -0000
Message-ID: <qoednbGWLZwC-TvcRVn-1A_at_giganews.com>
Date: Thu, 25 Nov 2004 23:02:53 -0000
Message-ID: <qoednbGWLZwC-TvcRVn-1A_at_giganews.com>
The ANSI Standard syntax supported by both products is
UPDATE Table1
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) ;
SET city_id =
(SELECT T2.id
FROM Table2 AS T2
WHERE T2.city = Table1.city)
(SELECT *
FROM Table2 AS T2
WHERE T2.city = Table1.city) ;
-- David Portas SQL Server MVP --Received on Fri Nov 26 2004 - 00:02:53 CET