none
how update 3 fields?

    Question

  • hi,

    we want change the value of 2fields Firstname and Last name

    (

    select *
    from Users
    where UserName in(   '45130502','100054135','44227003','17813407','45416208','100046052','45327603','48669202','47913001','47213600','45529701','44763004','48712900','45130005','28813304','100026021','47813407','45416007','100046053','45327709','100053663','48669107','100053612','47912907','100057193','45552306','44763205','48712900','4513047','4153007','100001567','100011223','100053663','45211601','60709680','45210800','64016604','48610405','48610500','46822409','46822504','45612707','45616006'
    )

    )

    this code is true?

    update users

        set @temp=Firsname

        set Firstname=Lastname

        set lastname=@temp

    where userename in(...)

    Saturday, October 19, 2013 5:15 AM

Answers

  • You do not need a variable to do this. just update it:

    UPDATE  users
    SET     Firstname = Lastname ,
            Lastname = Firsname
    where userename in(...)


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Saturday, October 19, 2013 5:26 AM
  • Try this (not tested),

    select firstname,lastname,username into #temp from users where username in (...)
    update users set b.firstname = a.lastname, b.lastname = a.firstname
    from #temp a inner join users b 
    on a.username=b.username


    Regards, RSingh

    Saturday, October 19, 2013 5:26 AM

All replies

  • Try this (not tested),

    select firstname,lastname,username into #temp from users where username in (...)
    update users set b.firstname = a.lastname, b.lastname = a.firstname
    from #temp a inner join users b 
    on a.username=b.username


    Regards, RSingh

    Saturday, October 19, 2013 5:26 AM
  • You do not need a variable to do this. just update it:

    UPDATE  users
    SET     Firstname = Lastname ,
            Lastname = Firsname
    where userename in(...)


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Saturday, October 19, 2013 5:26 AM
  • This behavior is called "All-at-Once". This means that all expressions in the same logical query process phase (here is SELECT) are evaluated logically at the same time.

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Saturday, October 19, 2013 5:29 AM
  • hi deer friend,

    we use of our code but we have this error

    The multi-part identifier "b.firstname" could not be bound

    why?

    Sunday, October 20, 2013 5:58 AM
  • Hi Seyed, It seems like the alias name is not working fine in this case. You can try the below query, it is working. (Tested)

    create table users(firstname varchar(10),lastname varchar(10),username varchar(10))
    insert into users values('a','b','1')
    insert into users values('c','k','2')
    select firstname,lastname,username into #temp from users where username in (1)
    --select * from #temp
    update users
    set users.firstname = #temp.lastname, users.lastname = #temp.firstname
    from #temp inner join users 
    on users.username=#temp.username


    Regards, RSingh

    Sunday, October 20, 2013 8:42 AM
  • tanks,
    Sunday, October 20, 2013 9:15 AM