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 2008 Forums
 Transact-SQL (2008)
 update multiple records

Author  Topic 

PGG_CA
Starting Member

24 Posts

Posted - 2013-09-24 : 15:28:28
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 16:41:30
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

172 Posts

Posted - 2013-09-24 : 18:46:30
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;
Go to Top of Page

PGG_CA
Starting Member

24 Posts

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-25 : 13:05:11
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 - 2013-09-26 : 11:16:32
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
   

- Advertisement -