Update multiple columns in a table based

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 2005 Forums

Transact-SQL (2005)

Update multiple columns in a table based

Author

Topic

itsarnie
Starting Member

18 Posts

Posted - 2009-09-13 : 10:05:46

Hi,

I want to update a column for multiple rows in a table based on condition from the sample table.

Table name is SMPL
column to update is SMPL_ID,SEQ_ID

SMPL_ID SEQ_ID
1 5
2 5
66 52
55 9
66 9
7 10
7 52

I need to update(if duplicates found) SMPL_ID as 09+SMPL_ID for the records SEQ_ID=52

so records for 52 will be like

0966 52
097 52

Unable to update because of multiple updates.

Please help.

Thanks,
Arnie

Thanks and regards
Arnie,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-13 : 10:33:32

If SMPL_ID is of type INT or other numeric type then it is not possible to have leading zeroes.
Otherwise you can do
update SMPL
set SMPL_ID = '09' + SMPL_ID
where SEQ_ID = 52
and exists (select SEQ_ID from SMPL where SEQ_ID = 52 group by SEQ_ID having count(*) > 1)

But I can imagine you won't do that for hard coded values like '09' and 52 only?

No, you're never too old to Yak'n'Roll if you're too young to die.

itsarnie
Starting Member

18 Posts

Posted - 2009-09-13 : 10:41:34

Thanks so much for the reply.

Actually there can't be any dupes within 52...
We need to search for dupes across SEQ_ID...

like:
7 10
7 52
and will have to make it
7 10
097 10

Thanks,
Arnie

Thanks and regards
Arnie,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-13 : 10:48:41

Sorry but now I understand nothing.
On what condition becomes
7 10 -> 7 10
7 52 -> 097 10
?

No, you're never too old to Yak'n'Roll if you're too young to die.

itsarnie
Starting Member

18 Posts

Posted - 2009-09-13 : 10:57:05

If a SMPL_ID exists for 52 and also for any other SEQ_ID, that SMPL_ID needs to be updated as '09'+SMPL_ID.

Thanks,
Arnie

Thanks and regards
Arnie,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 12:57:03

quote:
Originally posted by itsarnie

If a SMPL_ID exists for 52 and also for any other SEQ_ID, that SMPL_ID needs to be updated as '09'+SMPL_ID.

Thanks,
Arnie

Thanks and regards
Arnie,

thats ok but how SEQ_ID for second record changed to 10?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources