selectively update tblA.colA from tblB.colA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

selectively update tblA.colA from tblB.colA

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-08-10 : 16:16:09

Hi,

Could some body please help me. I want to Update tblA.colA with tblB.colA where tblB.DteDate has the min value.

I remember I saw some one has a solution for this, but couldn't find it.

Thanks!

--create and populate #tblA
IF OBJECT_ID('tempdb..#tblA') IS NOT NULL DROP TABLE #tblA
CREATE TABLE [#tblA] (
[PKID] INT,
[colA] INT NULL
) ON [PRIMARY]

GO
Insert INTO #tblA Values(1, 10)
Insert INTO #tblA Values(2, 10)
Insert INTO #tblA Values(3, 10)
--create and populate #tblB
IF OBJECT_ID('tempdb..#tblB') IS NOT NULL DROP TABLE #tblB
CREATE TABLE [#tblB] (
[FKID] INT,
[colA] INT NULL,
[DteDate] datetime NULL
) ON [PRIMARY]

GO

Insert INTO #tblB Values(1, 5, '19790222')
Insert INTO #tblB Values(1, 6, '20050201')
Insert INTO #tblB Values(3, 1, '20061212')

--check the data
SELECT * FROM #tblA
SELECT * FROM #tblB

--this first UPDATE wouldn't run and the second one got the wrong data, just as you can see in the SELECT, which grabbed both 5 & 6 for FKID = 1
UPDATE #tblA
SET colA = B.colA
FROM #tblA JOIN (SELECT FKID, MIN(DteDate) AS minDte FROM #tblB GROUP BY FKID) B on #tblA.PKID = B.FKID

UPDATE #tblA
SET colA = B.colA
FROM #tblA JOIN (SELECT FKID, colA, MIN(DteDate) AS minDte FROM #tblB GROUP BY FKID, colA) B on #tblA.PKID = B.FKID

SELECT B.*
FROM #tblA JOIN (SELECT FKID, colA, MIN(DteDate) AS minDte FROM #tblB GROUP BY FKID, colA) B on #tblA.PKID = B.FKID

--By the way, the solution cannot use a #table, because this will be in a UDF.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-10 : 17:00:52

Doh!!!!!!!!!!!!!!!!

Ashley Rhodes

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-08-13 : 10:43:58

Well, I think I need to twist the subquery in certain way...
Basically, this next UPDATE is wrong in tsql syntax (the WHERE part), but it reflects the logic behind the requirement.

UPDATE A
SET A.colA = B.colA
FROM #tblA A JOIN #tblB B ON A.PID = B.FKID
WHERE MIN(B.DteDate) = TRUE

There got to be a way around this without using #temp table or cursor.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-08-21 : 09:45:51

Ok, I found it.

SELECT A.PKID, A.colA, B.FKID, b.colA
from #tblA A join #tblB B on A.PKID = B.FKID
where B.DteDate in (Select min(DteDate) from #tblB Group by FKID)

UPDATE #tblA
SET colA = B.colA
from #tblA A join #tblB B on A.PKID = B.FKID
where B.DteDate in (Select min(DteDate) from #tblB Group by FKID)

And there are another two ways to handle this in sql2000, using Top and subquery. Then in sql2005, I think, OVER() will also work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources