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 2008 Forums
 Transact-SQL (2008)
 update multiple records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PGG_CA
Starting Member

24 Posts

Posted - 09/24/2013 :  15:28:28  Show Profile  Reply with Quote
Using SQL Server 2008, here's what I want to achieve:

MyTable
id status startdate job
1 Active 2013-06-30 P
1 Active 2013-02-25 P
2 Active 2012-12-12 P
2 Active 2013-01-01 P
3 Active 2013-08-08 P
3 Active 2013-08-02 P

Id is supposed to be the primary key but the data being imported to this table contains multiple records for same IDs so PK is not enforced. I need to update the tabel so that I can only have one unique id for a record. I need to compare the startdate for the same record ID, get the older date and update that record by appending a P to it. In the end the records shoulsd look like this:

MyTable
id status startdate job
1 Active 2013-06-30 P
1P Active 2013-02-25 P
2P Active 2012-12-12 P
2 Active 2013-01-01 P
3 Active 2013-08-08 P
3P Active 2013-08-02 P

Thanks for any help.

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 09/24/2013 :  16:41:30  Show Profile  Reply with Quote
Is the column ID numeric type, or is it character type? If it is numeric type, you would need to alter that to character type first. Then
;WITH cte AS
(
	SELECT id, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY startdate DESC) AS RN
)
UPDATE cte SET id = id+'P' WHERE RN > 1;
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/24/2013 :  18:46:30  Show Profile  Reply with Quote
quote:

;WITH cte AS
(
	SELECT id, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY startdate DESC) AS RN
        FROM table_name
)
UPDATE cte 
SET id = id+'P' 
WHERE RN > 1;



based on sample data,

update d
set d.id+='P'
from table_name d
inner join
(
select id, min(startdate) mi
from table_name
group by id
)t
on d.id = t.id
and d.startdate = t.mi;

Alternative syntax:

update table_name
set table_name.id+='P'
from
(
select id, min(startdate) mi
from table_name
group by id
)t
where table_name.id = t.id
and table_name.startdate = t.mi;

Edited by - sigmas on 09/24/2013 18:53:15
Go to Top of Page

PGG_CA
Starting Member

24 Posts

Posted - 09/25/2013 :  12:00:15  Show Profile  Reply with Quote
Thanks for your inputs everyone. I ended up using James' but I learned something from sigmas.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 09/25/2013 :  13:05:11  Show Profile  Reply with Quote
You are welcome; but you did see that my query was incorrect and sigmas fixed it, right? :)
Go to Top of Page

PGG_CA
Starting Member

24 Posts

Posted - 09/26/2013 :  11:16:32  Show Profile  Reply with Quote
quote:
Originally posted by James K

You are welcome; but you did see that my query was incorrect and sigmas fixed it, right? :)



YES and thanks again.
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.07 seconds. Powered By: Snitz Forums 2000