Updating using multiple values

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)

Updating using multiple values

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-03-24 : 08:30:25

Good morning,

I am trying to write a query that checks for duplicates and then creates a code that tell the user where the other record can be found. This is what I have:

Update tblFailedUpLoad
set FailedCode = Source
FROM tblFailedUpLoad
WHERE tblFailedUpLoad.TrailerID In
(SELECT TrailerID
FROM tblFailedUpLoad As Tmp
GROUP BY TrailerID,UpLoadDate,TrailerType,Source
HAVING Count(*)> 1
And UpLoadDate = tblFailedUpLoad.UpLoadDate
And TrailerType = tblFailedUpLoad.TrailerType
and Source <> tblFailedUpLoad.Source
AND tblFailedUpLoad.UpLoadDate =convert(varchar, Getdate() , 101))

What I want it to do is update based on the other value. I can find lots of posts on how to based on the same value in another table and I have no problem with that. This one is causing me fits....

Thanks

Laura

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-24 : 08:59:44

This is basically what you are doing if you make this a derived table.

UPDATE fu1
SET fu1.FailedCode = fu2.Source
FROM
tblFailedUpLoad fu
INNER JOIN (
SELECT fu2.TrailerID, fu2.UpLoadDate, fu2.TrailerType, fu2.Source, Count(fu2.TrailerID) AS Counter
FROM tblFailedUpLoan fu2
GROUP BY fu2.TrailerID, fu2.UpLoadDate, fu2.TrailerType, fu2.Source
HAVING COUNT(fu2.TrailerID)>1) fu3 ON fu.TrailerID = fu3.TrailerID
AND fu.UpLoadDate = fu3.UpLoadDate
AND fu.TrailerType = fu3.TrailerType
WHERE
fu.UpLoadDate = CONVERT(VARCHAR(GETDATE(),101))
AND fu.Source <> fu3.Source

If you look at your join, you will get counts>1 only if all the fields in the derived table are duplicated. You might want to look at adding a third level if all you want is where the TrailerID has count>2. This will not give that to you if you have two TrailerIDs duplicated, but any of the other columns are different. Make sense?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-03-24 : 09:13:08

Thanks so much Derrick. Yes it does make sense.

Thanks

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources