Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slihp
Yak Posting Veteran

61 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
343 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  
 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.06 seconds. Powered By: Snitz Forums 2000