Posted by Serge Rielau on 06/08/05 05:59

Erland Sommarskog wrote:
> Any time I see someone touting the ANSI syntax for UPDATE and DELETE,
> I will reserve the right to bump in. Unless portability is a true and
> serious concern, and just theoretical "it could happen", the ANSI
> syntax has so serious problems - both in itself, and specific to
> SQL Server - that it is in my opinion poor advice to suggest it
> instead of the FROM syntax which is clearer, easier to understand,
> and usually also more performant on SQL Server.
>
Just an FYI the ANSI syntax to solve the problem is

MERGE INTO <target>
USING <source>
ON <join codition>
WHEN MATCHED THEN UPDATE SET ...

IMHO The UPDATE with the double query is tough for many users to
understand, before MERGE the UPDATE FROM problem was a FAQ in
c.d.ibm-db2. UPDATE FROM is also supported by Informix XPS, btw.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

• Next in forum: Re: Backup strategy
• Prev in forum: Re: Does the transaction log on a publisher SQL server get truncated when the database is backed up, but the distributor is down?
• Thread view: Re: SELECT works but UPDATE fails. ?

[Reply to this message]