Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: Challenge - Swap two columns

    Ok .. a challenge ...

    How do you swap two columns based on some condition without using a cursor ( I mean a set based solution).
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    alter table foo add tempcol

    update foo set tempcol=field1

    update foo set field1 = field2

    update foo set field2=tempcol

    alter table drop column tempcol


    you did not say it had to be good, you did not say it had to be efficient, you said merely no cursor
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    did you forget it is not possible to add a column and update it in the same sql batch ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    forget? no, because i never knew that to begin with

    okay, so just run those steps in separate batches
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    forget? no, because i never knew that to begin with
    You are joking ??? Right ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not in the slightest, no, i was not joking

    i don't know what a "batch" is

    i just know it's something you DBAs concern yourself with

    is a batch like a transaction block? isn't there a COMMIT statement you can use?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by r937
    not in the slightest, no, i was not joking

    i don't know what a "batch" is

    i just know it's something you DBAs concern yourself with

    is a batch like a transaction block? isn't there a COMMIT statement you can use?
    "Go" and Read up on "GO" statement in the holy book ....
    Also
    Try this
    Code:
    use pubs
    go
    create table Mytable99 (a int)
    go
    insert into Mytable99 select 1
    go
    begin tran
    alter table MyTable99 
    add b int
    commit tran
    update MyTable99 set b = 2
    go
    Then Try this
    Code:
    drop table MyTable99
    go
    use pubs
    go
    create table Mytable99 (a int)
    go
    insert into Mytable99 select 1
    go
    begin tran
    alter table MyTable99 
    add b int
    commit tran
    go
    update MyTable99 set b = 2
    go
    
    drop table MyTable99
    go
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Enigma
    Try this
    Code:
    use pubs
    go
    create table Mytable99 (a int)
    go
    insert into Mytable99 select 1
    go
    begin tran
    alter table MyTable99 
    add b int
    commit tran
    update MyTable99 set b = 2
    go
    Error: Invalid column name 'b'. (State0022, Native Code: CF)

    Then Try this
    Code:
    drop table MyTable99
    go
    use pubs
    go
    create table Mytable99 (a int)
    go
    insert into Mytable99 select 1
    go
    begin tran
    alter table MyTable99 
    add b int
    commit tran
    go
    update MyTable99 set b = 2
    go
    
    drop table MyTable99
    go
    This command did not return data, and it did not return any rows

    sweet

    and your point is?

    and how does this swap two columns??????????????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2004
    Posts
    1
    I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

    update table99 set a=b, b=a

    with an optional where condition.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by nm2
    I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

    update table99 set a=b, b=a

    with an optional where condition.
    At least the way that I understand Enigma's question, your answer is exactly what I would have suggested. The part that I'm still fuzzy on is that the rest of this thread seems to have veered off on a tangent, which makes me wonder if I understood the original question.

    -PatP

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    That was the answer I was looking for .... coz I was intrigued by the way sql server handles updates in this case

    R937 : the point is the sql batch .... all statements in a sql batch are compiled into one execution plan ... and you cannot alter and update in the same sql batch .... Transaction has no effect on a sql batch ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by nm2
    I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

    update table99 set a=b, b=a

    with an optional where condition.


    Not a shining moment for the gurus of dbforums.com...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yep. For those geeky enough to care, the ISO standard calls for all of the RValues in an UPDATE statement to evaluate before any of the LValues are resolved. This means that all of the computations have to be complete before the first change is allowed to happen.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •