Update related felids - 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 2005 Forums

Transact-SQL (2005)

Update related felids

Author

Topic

slihp
Yak Posting Veteran

61 Posts

Posted - 2014-06-16 : 13:47:13

Hi there

im not sure how to do this as im not great with update statements. I have a table that get populated with related ids. basically i want to periodicaly update the table and set the related ids to the last know id that was entered, so in the case below

this set

ID | LinkID | Active | Date
z5 | k2 | 0 | 01/01
s6 | k2 | 0 | 17/02
d7 | k2 | 0 | 21/03
d8 | k2 | 0 | 20/04
d9 | k2 | 1 | 02/05

would be updated to this

ID | LinkID | Active | Date
d9 | k2 | 0 | 01/01
d9 | k2 | 0 | 17/02
d9 | k2 | 0 | 21/03
d9 | k2 | 0 | 20/04
d9 | k2 | 1 | 02/05

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-06-16 : 14:56:14

You do not specify what the primary key of the table is, so it is impossible to know if you are going to generate a unique key violation. Additionally, you do not clearly state what the business rule(s) are. Finally, from the looks of it, I want to state that this design pattern does not appear to be a best practice. I strongly advise you to provide some context around this as there may be a better way to either structure your data to avoid this or provide the result you are looking for without this update.

In any case, I did my best to answer your question:


DECLARE @T1 TABLE 
(
ID CHAR(2) NOT NULL,
LinkID CHAR(2) NOT NULL,
Active BIT NOT NULL,
[Date] DATE NOT NULL
);

INSERT @T1 
SELECT 'a1' , 'k1' , 0 , '2014-01-05' UNION ALL
SELECT 'g9' , 'k1' , 1 , '2014-02-13' UNION ALL
SELECT 'z5' , 'k2' , 0 , '2014-01-01' UNION ALL
SELECT 's6' , 'k2' , 0 , '2014-02-17' UNION ALL
SELECT 'd7' , 'k2' , 0 , '2014-03-21' UNION ALL
SELECT 'd8' , 'k2' , 0 , '2014-04-20' UNION ALL
SELECT 'd9' , 'k2' , 1 , '2014-05-02';

SELECT * FROM @T1;

UPDATE t
SET	ID = t2.ID
FROM @T1 AS t
JOIN @T1 AS t2
ON t.LinkID = t2.LinkID
WHERE t.Active = 0
AND t2.Active = 1
AND t.ID <> t2.ID

SELECT * FROM @T1;
/*
ID	LinkID	Active	Date
g9	k1	0	2014-01-05
g9	k1	1	2014-02-13
d9	k2	0	2014-01-01
d9	k2	0	2014-02-17
d9	k2	0	2014-03-21
d9	k2	0	2014-04-20
d9	k2	1	2014-05-02
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources