Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
update multiple columns in single query based on different where clause in sql server 2000

Answered update multiple columns in single query based on different where clause in sql server 2000

  • Monday, January 28, 2013 6:21 AM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    I am using sql server 2000 and below is my scenario. The current design is incorrect and it is storing username instead by userid in the transacttable. I am trying to update this to user id for 3 columns (openby, closedby and edit by)

    create table #usertable
(
userid int,
username varchar(100)
)

create table #transacttable
(
transactID int, 
descr varchar(100),
openby varchar(100),
closedby varchar(100),
editby varchar(100)
)

insert into #usertable select 1,'john'
insert into #usertable select 2,'maxwell'
insert into #usertable select 3,'Rhodes'
insert into #usertable select 4,'Naomi'
insert into #usertable select 5,'Ted'


insert into #transacttable select 1,'a','Rhodes','Naomi', NULL
insert into #transacttable select 2,'b','Ted',NULL, 'maxwell'
insert into #transacttable select 3,'c','Naomi','john', 'Ted'

update a set openby = UsrDet.userid
from #usertable UsrDet (nolock)
INNER JOIN #transacttable  a  (nolock) ON UsrDet.username = a.openby


update a set closedby = UsrDet.userid
from #usertable UsrDet (nolock)
INNER JOIN #transacttable  a  (nolock) ON UsrDet.username = a.closedby

update a set editby = UsrDet.userid
from #usertable UsrDet (nolock)
INNER JOIN #transacttable  a  (nolock) ON UsrDet.username = a.editby




select * from #transacttable

drop table #transacttable
drop table #usertable

    I am able to achieve this using 3 different update queries. BUT is this possible using single update query???

    any help is greatly appreciated.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

     

All Replies

  • Monday, January 28, 2013 6:32 AM
     
     
    Sign In to Vote
    0

    As you said, your db design can be better implemented.However, lets look at the issue.

    I assume your way is best fit here. You may not need to make it single query as long as you do not have any issues. It is not somthing like the queries will be performed better if make it single execution. To me, rather it might be looking difficult sometimes. You may be able to acheive this with CASE WHEN. However, I wont suggest as long as you do not have any performance issues OR you may keep as it is.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Monday, January 28, 2013 6:56 AM
     
     
    Sign In to Vote
    0
    Yes there is performance issue.. I had provided only a sample no of records. But in real scenario the number of records are pretty huge and it is taking lot of time. That is why I wanted to do this in a single query. Please could anyone provide the case when query for this scenario?

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

     
  • Monday, January 28, 2013 7:03 AM
     
     
    Sign In to Vote
    0
    Rakesh, I still do not think its beacuase of your multiple queries. You may check the indexes created on the tables and how the execution plan looks like. Could you please share the execution plan?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Monday, January 28, 2013 7:10 AM
     
      Has Code
    Sign In to Vote
    0

    Try

    update a set 
openby = case when UsrDet.username = a.openby then  UsrDet.userid else a.openby end
,closedby = case when UsrDet.username = a.closedby then  UsrDet.userid else a.closedby end
,editby = case when UsrDet.username = a.editby then  UsrDet.userid else a.editby end
from #usertable UsrDet (nolock)
INNER JOIN #transacttable  a (nolock) ON UsrDet.username = in ( a.openby, a.closedby, a.editby )



    Serg

     
  • Monday, January 28, 2013 7:15 AM
     
     Answered Has Code
    Sign In to Vote
    2

    This is one possibility, doing all three updates in one shot, please check if you are getting a performance advantage here

    use tempdb ;
create table #usertable
(
userid int,
username varchar(100)
);

create table #transacttable
(
transactID int, 
descr varchar(100),
openby varchar(100),
closedby varchar(100),
editby varchar(100)
);

insert into #usertable select 1,'john';
insert into #usertable select 2,'maxwell';
insert into #usertable select 3,'Rhodes';
insert into #usertable select 4,'Naomi';
insert into #usertable select 5,'Ted';


insert into #transacttable select 1,'a','Rhodes','Naomi', NULL;
insert into #transacttable select 2,'b','Ted',NULL, 'maxwell';
insert into #transacttable select 3,'c','Naomi','john', 'Ted';

select * from #transacttable

update a set openby = User1.userid,
closedby = user2.userid,
editby = User3.userid
from #transacttable  a Inner join
#usertable User1 ON User1.username = a.openby
Left join #usertable user2 on User2.username = a.closedby
left join #usertable user3 on User3.username = a.editby;



select * from #transacttable;
drop table #transacttable;
drop table #usertable;


    Regards
    Satheesh

    • Marked As Answer by Iam_Rakesh Monday, January 28, 2013 7:35 AM
    •  
     
  • Monday, January 28, 2013 7:16 AM
     
     
    Sign In to Vote
    0

    Why are you concerned about stroing ID values in the varchar column ?

    Also ,remove Nolock as it can have some bad effect . Also nolock for Target table is useless...

    Thanks and regards, Rishabh K

     
  • Monday, January 28, 2013 7:28 AM
     
     
    Sign In to Vote
    0

    Try


    Update T1
    Set OpenBy = t2.OpenBy,ClosedBy = T2.ClosedBy,EditBy = t2.EditBy
    From #transacttable T1
    Inner Join (
    Select  TransactID,Descr ,OpenBy = Max(Case When OpenBy = UserName Then UserId End) ,
         ClosedBy = Max(Case When ClosedBy = UserName Then UserId End),
      EditBy = Max(Case When EditBy = UserName Then UserId End) 
    From #transacttable T1
    Outer Apply #usertable T2 
    Where T1.OpenBy = T2.UserName Or T1.ClosedBy = T2.UserName Or T1.EditBy = T2.UserName 
    Group By TransactID,Descr ) T2 On T1.TransactID = T2.TransactID And T1.Descr = T2.Descr

    Please have look on the comment