Re: Trigger to timestamp all Updates, tables with Multiple field Primary Key
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 12/02/04
- Next message: Barry McAuslin: "Re: Creating Job with ActiveX script"
- Previous message: GYK: "RE: How to identify and kill a query"
- Next in thread: Hugo Kornelis: "Re: Trigger to timestamp all Updates, tables with Multiple field Primary Key"
- Reply: Hugo Kornelis: "Re: Trigger to timestamp all Updates, tables with Multiple field Primary Key"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 1 Dec 2004 19:38:13 -0800
Anthony
Hugo was talking about the syntax of the UPDATE involving a join, not the JOIN per se.
Using a JOIN in an update in not ANSI standard; ANSI requires subqueries to get the same result.
Whether you express you join condition in a JOIN.. ON clause or a WHERE clause is not the issue here.
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"AnthonyThomas" <Anthony.Thomas@CommerceBank.com> wrote in message news:eh9SuBB2EHA.3392@TK2MSFTNGP10.phx.gbl...
Um, I beg to differ. The JOIN syntax is the non-proprietary, ANSI SQL92 standard!
Prior to the JOIN syntax, there was no ANSI standard and vendors made up their own. Microsoft ported the WHERE t1.c1 = t2.c1 syntax for INNER join queries and WHERE t1.c1 *= t2.c1 syntax for LEFT OUTER join queries.
Not only will the JOIN syntax perform better, but it will support non-equal joins--a feat the WHERE clause syntax could never replicate.
Sincerely,
Anthony Thomas
--
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:v1c2p0plut104ksc2ueqjnqe7ikmdpncdv@4ax.com...
On Tue, 09 Nov 2004 16:10:23 GMT, Keith Bromley via SQLMonster.com wrote:
>I would appreciate the benefit of your expertise in constructing an appropriate 'WHERE' clause for these circumstances! (Primary Key = OrderNo + ItemNo + Instance').
Hi Keith,
Mike has already posted an answer using the Transact SQL proprietary
UPDATE ... FROM syntax. If you prefer to use the ANSI-standard UPDATE
syntax, try this instead:
UPDATE dbo.Instances
SET LastUpdate = CURRENT_TIMESTAMP
WHERE EXISTS (SELECT *
FROM inserted
WHERE Instances.FtNum = inserted.FtNum
AND Instances.ItemNo = inserted.ItemNo
AND Instances.Instance = inserted.Instance)
(Note - the proprietary UPDATE ... FROM sually performs faster than this
ANSI compliant version - but if course, the ANSI cersion is easier to
port).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Barry McAuslin: "Re: Creating Job with ActiveX script"
- Previous message: GYK: "RE: How to identify and kill a query"
- Next in thread: Hugo Kornelis: "Re: Trigger to timestamp all Updates, tables with Multiple field Primary Key"
- Reply: Hugo Kornelis: "Re: Trigger to timestamp all Updates, tables with Multiple field Primary Key"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
