SQL Server Performance

Swaping Columns in Single Query

Discussion in 'General Developer Questions' started by vamsimusunuru, Aug 25, 2006.

  1. vamsimusunuru New Member

    Hi,

    I Have Table called Numbers which Contains Columns Num1 and Num2 of same data type. I want a Single Query to swap the rows in the Table. The condition is not to use Temporary Table.
  2. FrankKalis Moderator


    UPDATE Numbers SET Num1 = Num2, Num2 = Num1

    Please do your future homework yourself. This is basic stuff.
    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. Bredsox New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Please do your future homework yourself. This is basic stuff.<br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Frank,<br /> I agree even though sometimes it's tricky. Cheers. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  4. mmarovic Active Member

    Well, you could really have tested that transaction with go yourself.
  5. mmarovic Active Member

    Bradsox, you edited your post so my answer now doesn't make sense. Not nice and realy unnecessary. Anyway, next time I will quote the sentence I am replying to.
  6. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />UPDATE Numbers SET Num1 = Num2, Num2 = Num1<br /></font id="code"></pre id="code"><br />Please do your future homework yourself. This is basic stuff.<br />--<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Boy, the homework was about swapping the rows, not columns [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  7. FrankKalis Moderator

    LOL, very good catch! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />I think then vamsimusunuru has to explain more specifically his problem, the table structure, sample data and desired result.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  8. Madhivanan Moderator

    Swap the rows doesnt make sense until expected result is given

    Madhivanan

    Failing to plan is Planning to fail
  9. Bredsox New Member

    quote:Originally posted by mmarovic

    Bradsox, you edited your post so my answer now doesn't make sense. Not nice and realy unnecessary. Anyway, next time I will quote the sentence I am replying to.


    LOL, I didn't know you were referring to my post.[:0]

  10. mmarovic Active Member

    Bradsox: no big deal.<br /><br />Madhivanan: That's true. There is no order in table, so what does swaping rows mean? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  11. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br />so what does swaping rows mean? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />To me, it means keeping the PK exact and interchanging all other column values.<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  12. FrankKalis Moderator

    quote:
    To me, it means keeping the PK exact and interchanging all other column values.
    Yes, but according to what logic? Swap all values by 1 "position" or what? We should give the OP the chance to explain his problem, if he still is following his thread.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  13. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Yes, but according to what logic? Swap all values by 1 "position" or what? We should give the OP the chance to explain his problem, if he still is following his thread.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I used to ask a similiar question in interviews. It si smoething like, with the following test data<br /><br />CREATE TABLE #Test(sname varchar(10) primary key , position int)<br />INSERT INTO #Test VALUES('Frank',3)<br />INSERT INTO #Test VALUES('Satya',1)<br />INSERT INTO #Test VALUES('Adrian',4)<br />INSERT INTO #Test VALUES('Louis',2)<br /><br />SELECT * FROM #Test<br /><br /><br />Your task is to Swap the position of Louis and Frank [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  14. Adriaan New Member

    Nice to see you kept your own name out of that sample data.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  15. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br />I used to ask a similiar question in interviews. It si smoething like, with the following test data<br /><br />CREATE TABLE #Test(sname varchar(10) primary key , position int)<br />INSERT INTO #Test VALUES('Frank',3)<br />INSERT INTO #Test VALUES('Satya',1)<br />INSERT INTO #Test VALUES('Adrian',4)<br />INSERT INTO #Test VALUES('Louis',2)<br /><br />SELECT * FROM #Test<br /><br /><br />Your task is to Swap the position of Louis and Frank [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />UPDATE #Test<br /> SET position =<br /> CASE <br /> WHEN sname = 'Frank'<br /> THEN (SELECT position FROM #Test WHERE sname = 'Louis')<br /> ELSE (SELECT position FROM #Test WHERE sname = 'Frank') <br /> END<br /> WHERE sname IN ('Frank', 'Louis') <br /></font id="code"></pre id="code"><br />Do I have the job? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  16. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />Do I have the job? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />No. No subqueries, No derived tables, No temp tables. Just a single update.<br /><br />Try again, you can still get the job [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  17. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br />so what does swaping rows mean? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />To me, it means keeping the PK exact and interchanging all other column values.<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">I thought the same but then I looked at the original post:<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">I Have Table called Numbers which Contains Columns Num1 and Num2 of same data type. I want a Single Query to swap the rows in the Table.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  18. Adriaan New Member

    I agree it shouldn't be done with an update, but just to show it's really not difficult even without subqueries ...

    UPDATE t1 SET t1.position = t2.position
    FROM #test t1
    INNER JOIN #test t2
    ON (t1.sname = 'Frank' and t2.sname = 'Louis')
    OR (t2.sname = 'Frank' and t1.sname = 'Louis')
  19. Roji. P. Thomas New Member

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  20. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]Sometimes I do seem to think to complicated. <br />Here's another possible solution:<br /><pre id="code"><font face="courier" size="2" id="code"><br />UPDATE t1<br /> SET t1.position<br /> = CASE <br /> WHEN t1.sname = 'Frank'<br /> THEN t2.position<br /> WHEN t1.sname = 'Louis'<br /> THEN t2.position<br /> ELSE 0 END<br /> FROM #Test t1, #Test t2<br /> WHERE t1.sname IN ('Frank', 'Louis') <br /> AND t2.sname IN ('Frank', 'Louis') <br /> AND t2.sname != t1.sname<br /></font id="code"></pre id="code"><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  21. Adriaan New Member

    Frank, that's still a little overcomplicated ...[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />
  22. FrankKalis Moderator

    Yes, but I can't think of something more simple right now. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  23. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]Sometimes I do seem to think to complicated. <br />Here's another possible solution:<br /><pre id="code"><font face="courier" size="2" id="code"><br />UPDATE t1<br /> SET t1.position<br /> = CASE <br /> WHEN t1.sname = 'Frank'<br /> THEN t2.position<br /> WHEN t1.sname = 'Louis'<br /> THEN t2.position<br /> ELSE 0 END<br /> FROM #Test t1, #Test t2<br /> WHERE t1.sname IN ('Frank', 'Louis') <br /> AND t2.sname IN ('Frank', 'Louis') <br /> AND t2.sname != t1.sname<br /></font id="code"></pre id="code"><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />This should do. <br />&lt;edit&gt;<br /> I missed something here<br /><br />UPDATE t1 SET t1.position = t2.position<br />FROM #test t1<br />INNER JOIN #test t2<br />ON (t1.sname = 'Frank' and t2.sname = 'Louis')<br />OR (t2.sname = 'Frank' and t1.sname = 'Louis')<br />&lt;/edit&gt;<br /><br />But still we can further negotiate about the job [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  24. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />But still we can further negotiate about the job <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Sure, why not. I'm all ears. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  25. Chappy New Member


    update
    odd
    set
    odd.position = CASE
    WHEN (odd.position % 2) = 0
    THEN odd.position - 1
    ELSE odd.position + 1
    END
    from
    #Test odd
    INNER JOIN #Test even ON (odd.position+1 = even.position)


  26. FrankKalis Moderator

    Hm, I wonder if it is allowed to cheat a bit. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @i INT<br />SET @i = 1<br />UPDATE #Test<br /> SET @i = position = @i + 1<br /> WHERE sname IN ('Louis', 'Frank')<br /><br />SELECT * <br /> FROM #Test <br /> ORDER BY position<br /><br /><br />sname position <br />---------- ----------- <br />Satya 1<br />Frank 2<br />Louis 3<br />Adrian 4<br /><br />(4 row(s) affected)<br /></font id="code"></pre id="code"><br />Looks simple enough to me. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  27. FrankKalis Moderator

    Now counterquestion!<br /><br />Anybody cares to explain why 'Frank' is decremented? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  28. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Now counterquestion!<br /><br />Anybody cares to explain why 'Frank' is decremented? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I thought he got a promotion! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  29. FrankKalis Moderator

    At least, he should get one! [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  30. Adriaan New Member

    Since the position was changed I suppose the intention was to demote Luis. Frank just got in the way.<br /><br />For me, the only way is still up.[}<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />][xx(]
  31. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br />For me, the only way is still up.[}<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />][xx(]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Can you name the person who appeared LAST in the list ? [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  32. Adriaan New Member

    Luis was the last to be inserted in your script, but I had the highest ranking number. And according to Frank, to receive a higher ranking number is a demotion.
  33. FrankKalis Moderator

    ...according to the Dilbert principle or was it called downsizing? [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  34. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />...according to the Dilbert principle or was it called downsizing? [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />We three are responsible for hijacking several threads out here. <br /><br />Hope someday the administrators will stop us from deviating [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  35. Bredsox New Member

    Now I see why we need to do some more homework. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />Peace out yo!
  36. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />...according to the Dilbert principle or was it called downsizing? [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />We three are responsible for hijacking several threads out here. <br /><br />Hope someday the administrators will stop us from deviating [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I don't think we really hijacked this thread. The OP should have now several ways to tackle his problem.<br /><br />...not to speak of some other threads. [:I]<br />But, hey, this is a community and one of the strongest points in a community is socialising. Every now and then it is "allowed" and necessary to drift off-topic as the discussion flows. I don't like this "hit and run" mentality in communities: Ask question -&gt; Get answers -&gt; Good-bye. No, thanks! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  37. vamsimusunuru New Member

    Thanks for spending your Valuble time on this thread.

Share This Page