Tricky SQL? - SQL Server Forums

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

Transact-SQL (2000)

Tricky SQL?

Author

Topic

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-01-05 : 09:43:01

Say you have the following table :

[CODE]
CREATE TABLE ALLMEMBERS (
PERSONKEY INT,
MEMBERKEY INT,
SURNAME VARCHAR(30),
MEMBERSTATUS VARCHAR(2),
LASTUPDATE DATETIME,
DUPFLAG SMALLINT,
CREATEFLAG SMALLINT
) ON [PRIMARY]
GO

INSERT INTO ALLMEMBERS (PERSONKEY, MEMBERKEY, SURNAME, MEMBERSTATUS, LASTUPDATE, DUPFLAG, CREATEFLAG)
SELECT 370, 10590, 'LAWRENCE', 'A', '1942-08-14', 0, 0 UNION ALL
SELECT 371, 10590, 'LAWRENCE', 'A', '1942-08-14', 0, 0 UNION ALL
SELECT 472, 10713, 'CROMBIE', 'AC', '2003-10-13', 0, 0 UNION ALL
SELECT 473, 10713, 'CROMBIE', 'PP', '2003-08-15', 0, 0 UNION ALL
SELECT 473, 10713, 'CROMBIE', 'PP', '2003-08-15', 0, 0
[/CODE]

The problem is one of isolating duplicate records and picking the correct one to populate from. For each duplicate MEMBERKEY, the record with the latest LASTUPDATE date is the one I want to mark. Where this is the same for both, I would choose by STATUS, or if these are also identical, the first record found. When the latest record is found I then want to set the CREATEFLAG to 1.

Any ideas how to do this in a simple update statment?

________________
Make love not war!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-05 : 10:02:57

Maybe some variation of the following will stimulate some thought:

update allmembers set createflag = 1 from allmembers a,
(
	select a.memberkey, (select top 1 personkey from allmembers a2 where a2.memberkey = a.memberkey) personkey
	from allmembers a
	group by memberkey
) d
where d.personkey = a.personkey

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-01-05 : 10:09:50

Ehorn,

Thanks mate.

________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources