Convert SQL Update/From to ANSI Update

Question

Post reply

Convert SQL Update/From to ANSI Update

Jeff Moden

SSC Guru

Points: 1000539
More actions
February 28, 2006 at 6:49 am

#111710

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)
Intro to Tally Tables and Functions

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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)
Intro to Tally Tables and Functions