Get latest records for update/insert

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)

Get latest records for update/insert

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2004-04-22 : 12:34:03

I have a queue table of changes. I want to insert or update based on an existence check. I don't want to check all the records only the newest ones. Like this:

existsindb 1 =for exists waiting to update
0 =for not sure yet was sent to queue needs checking

fileid createddate existsindb
100 3/11 1
100 3/12 0
100 3/12 0
100 3/13 0
200 3/12 0
200 3/13 0

I need to get the most current info for the fileids and reset the
existsindb flag for a later update. 3/13 for both recs is the info I want.

Any ideas for a SET based method.

Thanks

slow down to move faster...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-04-22 : 21:41:00

[code]select fileid,createddate,existsindb
from queue q
join
(
select max(createddate) max_date
from queue
) d on d.max_date = q.createddate
[/code]

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-23 : 10:47:29

Do you mean like:


USE Northwind
GO

CREATE TABLE myTable99(fileid int, createddate varchar(10), existsindb int)
GO

INSERT INTO myTable99(fileid, createddate, existsindb)
SELECT 100, '3/11', 1 UNION ALL
SELECT 100, '3/12', 0 UNION ALL
SELECT 100, '3/12', 0 UNION ALL
SELECT 100, '3/13', 0 UNION ALL
SELECT 200, '3/12', 0 UNION ALL
SELECT 200, '3/13', 0
GO

SELECT * 
  FROM myTable99 a 

SELECT * 
  FROM myTable99 a 
 WHERE createddate = (SELECT MAX(createddate) FROM myTable99)

UPDATE a
   SET existsindb = 1
  FROM myTable99 a 
 WHERE createddate = (SELECT MAX(createddate) FROM myTable99)

SELECT * 
  FROM myTable99 a 
GO

DROP TABLE myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources