update multiple records - 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 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;

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;

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.

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? :)

PGG_CA
Starting Member

24 Posts

Posted - 2013-09-26 : 11:16:32