SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Update related felids
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slihp
Yak Posting Veteran

60 Posts

Posted - 06/16/2014 :  13:47:13  Show Profile  Reply with Quote
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

Edited by - slihp on 06/16/2014 13:51:40

lazerath
Constraint Violating Yak Guru

USA
326 Posts

Posted - 06/16/2014 :  14:56:14  Show Profile  Reply with Quote
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
*/

Edited by - lazerath on 06/16/2014 15:10:02
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000