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

Transact-SQL (2005)

Multiple Update

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2010-03-29 : 04:31:52

Hi All,

I need to write an update statement to change the 'userid' in all the three 'orderid'
----------------------------------------------------------------------
create table #TableA (Orderid int, UserID int , UserName varchar(25))

insert into #TableA (Orderid,UserID,UserName)

select 1,1,'John'union all
select 1,2,'Rich' union all
select 1,3,'Cliff' union all

select 2,1,'Cliff' union all
select 2,2,'Bill' union all
select 2,3,'David' union all

select 3,1,'David' union all
select 3,2,'Cliff' union all
select 3,3,'Rich'

select * from #TableA
-- DROP Table #TableA

--------------------------------------------------------------------------------
Example:

I need to update orderid 1,2,3 with Cliff as UserID=1
and the old user, who was userid=1 needs to be updated with Cliff's Old 'userid'

My final output should look like below

------------------------------------------------------------------------------
Orderid,UserID,UserName
1, 3, John
1, 2, Rich
1, 1, Cliff

2, 1, Cliff
2, 2, Bill
2, 3, David

3, 2, David
3, 1, Cliff
3, 3, Rich
------------------------------------------------------------------------------------

I am able to do this using multiple select statements and update statements for each orderid separately,But i need your help and expertise to achieve this in the best possible way with minimum statements and for multiple orderids at the same time

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-29 : 04:45:21

The best way is to have a better data model.
You should have a user table with userid, name and so on.
In your Order table then you should only store the userid so this kind of problem could not happen.

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

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-29 : 04:48:51

Try this:

There may be better way to do this updation and in single update statement also..

I am doing the updation in two steps: (Time contraint to try out other ways )

UPDATE #TableA SET USERID = ST.USERID
FROM #TableA inner join
( SELECT ORDERID,USERID FROM #TableA TMP
WHERE TMP.USERNAME ='Cliff'
) AS ST
ON #TableA.OrderId = ST.ORDERID
AND #TableA.USERID = 1

UPDATE #TableA SET USERID = 1 WHERE USERNAME ='CLIFF'

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-29 : 04:51:48

If your expected output is this??

quote:
Originally posted by kneekill
------------------------------------------------------------------------------
Orderid,UserID,UserName
1, 3, John
1, 2, Rich
1, 1, Cliff

2, 2, Cliff
2, 1, Bill
2, 3, David

3, 2, David
3, 1, Cliff
3, 3, Rich
------------------------------------------------------------------------------------

Your query is..

update a set a.userid=b.s_no from #TableA a inner join
(Select *,row_number() over(partition by orderid order by orderid,username) as s_no from #TableA
) b on a.orderid=b.orderid and a.username=b.username

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

kneekill
Yak Posting Veteran

76 Posts

Posted - 2010-03-29 : 04:54:03

quote:
Originally posted by webfred

The best way is to have a better data model.
You should have a user table with userid, name and so on.
In your Order table then you should only store the userid so this kind of problem could not happen.

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

Hi webfred

I already have separate tables for orders and users, the problem is with the third table (Table A) in the example above userid is specific to the orderid in the TableA which specifies the permissions order (etc). In my real time problem 'Username' is the actual
userid.

sorry if i have confused you.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-29 : 04:56:02

Have you tried my solution?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

kneekill
Yak Posting Veteran

76 Posts

Posted - 2010-03-29 : 04:56:48

quote:
Originally posted by senthil_nagore

If your expected output is this??

quote:
Originally posted by kneekill
------------------------------------------------------------------------------
Orderid,UserID,UserName
1, 3, John
1, 2, Rich
1, 1, Cliff

2, 2, Cliff
2, 1, Bill
2, 3, David

3, 2, David
3, 1, Cliff
3, 3, Rich
------------------------------------------------------------------------------------

Your query is..

update a set a.userid=b.s_no from #TableA a inner join
(Select *,row_number() over(partition by orderid order by orderid,username) as s_no from #TableA
) b on a.orderid=b.orderid and a.username=b.username

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Hi senthil

Since i want cliff to be 1 in all the orders i don not want to update orderid=2 with cliff's userid=2

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-29 : 05:02:45

Is there any logic behind your requirement??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

kneekill
Yak Posting Veteran

76 Posts

Posted - 2010-03-29 : 05:03:49

Hi pk_bohra,

Thanks a lot for reply, your solution worked. how i can i achieve the same in one query

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-29 : 05:07:33

quote:
Originally posted by senthil_nagore

Is there any logic behind your requirement??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Logic is for each orderid he wants to update userid = 1 where username = cliff
and there should not be same userid so for those who have userid = 1 he wants to update their userid with old userid of cliff.
finally he want to swap the userid.

Vaibhav T

kneekill
Yak Posting Veteran

76 Posts

Posted - 2010-03-29 : 05:08:43

quote:
Originally posted by senthil_nagore

Is there any logic behind your requirement??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Yes Senthil,

AS i have explained webfred above. UserID is not actually the ID of the user but specific to the orderid (say it denotes the hierarchy for the orders ). i have just took this temp table to make it easy to explain . The requirement is to update few specific orders and change the hierarchy ( userid )

I already have separate tables for orders and users, the problem is with the third table (Table A) in the example above userid is specific to the orderid in the TableA which specifies the permissions order (etc). In my real time problem 'Username' is the actual
userid.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 05:09:02

[code]
DECLARE @UPDATED_ITEMS table
(
Orderid int,
UserID int
)

Update a
set a.UserID=1
OUTPUT INSERTED.Orderid,DELETED.UserID INTO @UPDATED_ITEMS
from #TableA a
WHERE a.UserName='Cliff'
AND a.UserID <> 1

UPDATE a
SET a.UserID=u.UserID
from #TableA a
JOIN @UPDATED_ITEMS u
ON u.OrderID=a.OrderID
WHERE a.UserName <> 'Cliff'
AND a.UserID = 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kneekill
Yak Posting Veteran

76 Posts

Posted - 2010-03-29 : 05:11:55

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by senthil_nagore

Is there any logic behind your requirement??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Logic is for each orderid he wants to update userid = 1 where username = cliff
and there should not be same userid so for those who have userid = 1 he wants to update their userid with old userid of cliff.
finally he want to swap the userid.

Vaibhav T

Thanks vaibhav for explaining it for me.
Can any one help me write the query in a single statment

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-29 : 05:13:31

Thanks Guys.. understood!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-29 : 05:21:40

Whats your problem to do in couple of steps as pk_bohra solution?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-29 : 05:22:07

quote:
Originally posted by kneekill

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by senthil_nagore

Is there any logic behind your requirement??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Logic is for each orderid he wants to update userid = 1 where username = cliff
and there should not be same userid so for those who have userid = 1 he wants to update their userid with old userid of cliff.
finally he want to swap the userid.

Vaibhav T

Thanks vaibhav for explaining it for me.
Can any one help me write the query in a single statment

Yup this might work for you...


UPDATE #TableA SET UserID = CASE WHEN a.UserID = 1 then b.UserID WHEN UserName = 'Cliff' THEN 1 ELSE a.UserID END
FROM #TableA a
INNER JOIN ( SELECT OrderID, UserID FROM #TableA WHERE UserName = 'Cliff') b
ON a.OrderID = b.OrderID

Vaibhav T

kneekill
Yak Posting Veteran

76 Posts

Posted - 2010-03-29 : 05:42:17

Thanks a lot for your time Vaibhav, Visakh and Bohra. All of your solutions worked for me
There is lot to learn from you guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources