Convert SQL Update/From to ANSI Update
-
The following example script finds the lowest date for each unique combination of the FT and Doc columns combined and updates the found rows by setting IRD to 1. How can I convert the UPDATE to one that doesn't use the SQL Server proprietary FROM clause? In other words, how do I convert this to an ANSI update? I've been doing SQL Server so long, I can't even think of ANSI
ways to do this anymore.--===== Create a temp table to play with
CREATE TABLE #yourtable(FT INT,Doc VARCHAR(10),Date DATETIME,IRD INT)
--===== Populate it with data to play with
INSERT INTO #yourtable
(FT,Doc,Date,IRD)
SELECT 1,'Text1','1/1/05',0 UNION ALL
SELECT 1,'Text1','1/2/05',0 UNION ALL
SELECT 2,'Text1','1/30/05',0 UNION ALL
SELECT 2,'Text3','2/2/05',0
--===== This solves the problem
UPDATE #yourtable
SET IRD = 1
FROM #yourtable yt,
(--Derived table finds the lowest date for each FT/Doc combo
SELECT FT,Doc,MIN(DATE) AS DATE
FROM #yourtable
GROUP BY FT,Doc
)d
WHERE yt.FT = d.FT
AND yt.Doc = d.Doc
AND yt.Date = d.Date
--===== This just verifies the results
SELECT * FROM #yourtable
Thanks for the help...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Not using the From join syntax
whether its pure ansi, I'm not sure
--===== This solves the problem
UPDATE #yourtable
SET IRD = 1
where exists (select *
from (select FT, Doc, min([Date]) AS [DATE]
from #yourtable
group by FT, Doc) AS d
where #yourtable.FT = d.FT
and #yourtable.Doc = d.Doc
and #yourtable.[Date] = D.[Date])
SELECT * FROM #yourtable
-
Thanks Ray... that's just what I was looking for.
Dunno if ANSI allows for derived tables but I guess I'm going to find out 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply