multiply row and condition update (Same table)

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)

multiply row and condition update (Same table)

Author

Topic

yashish444
Starting Member

3 Posts

Posted - 2009-02-02 : 03:10:49

Hi

I need to update more than 1 row with different condition (where clause) per row in the same table.

something like this:


UPDATE mytable SET name='josh' WHERE id='1' and lastName='watergate'

UPDATE mytable SET name='bob' WHERE id='2' and lastName='bluegun'

I want to merge those 2 statements into 1.
how can I do that in a most effective way ?

thanks

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-02-02 : 03:22:40

hi dear,

something like this

UPDATE mytable SET name= CASE lastName
WHEN 'watergate' THEN 'josh'
WHEN 'bluegun' THEN 'bob'
ELSE ''
END
WHERE ID IN(1,2)

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-02 : 04:02:24

[code]UPDATE x
SET x.Name = w.NewName
FROM MyTable AS x
INNER JOIN (
SELECT 'Josh' AS NewName, 1 AS ID, 'Watergate' AS LastName UNION ALL
SELECT 'Bob', 2, 'Bluegun'
) AS w ON w.ID = x.ID
WHERE x.LastName = w.LastName[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 04:07:11

nope this is not completely correct. what happens when you've a record with last name bluegun and id 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-02 : 04:15:16

Do you mean my suggestion?
It will not update that row, exactly as OP's two UPDATE statements.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 04:30:11

quote:
Originally posted by Peso

Do you mean my suggestion?
It will not update that row, exactly as OP's two UPDATE statements.

E 12°55'05.63"
N 56°04'39.26"

sorry not yours..i was replying to previous suggestion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources