Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

non-perl SQL question

by Anonymous Monk
on Dec 31, 2003 at 04:40 UTC ( #317829=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Can you swap two values in SQL in one statement?

For instance, if I have records with an "order" value, and want to swap six and five around so that six becomes five and five becomes six, can I do it in one statement?

I'm not in a position to test this at the moment.

I was thinking you'd have to temporarily set the "order" value five to null, or a million or something, then make six into five and the temporarily-changed record into six.

Because if you do it like this:

set order = 5 where order = 6 set order = 6 where order = 5
then you're just going to end up with two sixes, aren't you? And if you do "limit =1" then you don't know which one you're going to get.

Replies are listed 'Best First'.
Re: non-perl SQL question
by Roger (Parson) on Dec 31, 2003 at 05:28 UTC
    Just an idea, I haven't tested this yet...
    update table set order_id=11-order_id where order_id in (5,6)

      Clever solution!

      my $sql = sprintf "UPDATE table SET id = %d - id WHERE id IN ( %d, %d +)", ($val1+$val2), $val1, $val2; mysql> select * from test; +------+------+ | id | num | +------+------+ | 1 | 100 | | 2 | 200 | +------+------+ 2 rows in set (0.00 sec) mysql> UPDATE test SET id = 3 - id WHERE id IN (1,2); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from test; +------+------+ | id | num | +------+------+ | 2 | 100 | | 1 | 200 | +------+------+ 2 rows in set (0.00 sec) mysql>

      cheers

      tachyon

      Very clever solution (you see: it pays to have attended math-class!) but only if the field you try to swap is not a primary key field or indexed by "UNIQUE". Then it burps an error: #1062 - Duplicate entry '6' for key 1

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

        Thanks. ;-)

        I first had this idea 12 years ago, during a first year C programming lecture at University. The professor declared in the class that if you want to swap the values of two variables, you have to do it with a temporary variable:
        X=5 Y=6 # to swap X and Y, introduce a temporary variable Z Z=X X=Y Y=Z
        I challenged his declaration and provided the following solution in 2 steps without a third variable:
        X=X+Y Y=X-Y X=X-Y # I know this solution is not perfect: # when X and Y are big, there is a danger of overflow.
        The lecturer was amazed and gave me an 'A' for the subject. *grin*

        The solution set order_id=11-order_id where order_id in (5,6) is just another variant of the same trick.

        Update: Thanks rkg to point out the missing bit. My memory got rusty. The original challenge was to swap the values without using a third temporary variable.

Re: non-perl SQL question
by tachyon (Chancellor) on Dec 31, 2003 at 05:37 UTC

    To swap values like that you are probably going to have to use a temp value in your SQL. In most languages that support XOR you can Swap Two ints Without Using a Third Variable

    Here's an old trick from C. If you have two ints a and b whose values you want to swap, the obvious way to do so is with a third, temporary variable:

    int c = a; a = b; b = c;

    It can be done without a temporary variable, however, using the bitwise xor operator ^:

    a = a^b; b = a^b; a = a^b;

    The operator ^ produces a new int, each of whose bits is the result of xor-ing the two corresponding bits from the operands (1 if the bits are different, 0 otherwise). To see why the trick works, consider the single-bit case:

    of a=1 and b=0. a = a^b = 1 xor 0 = 1 b = a^b = 1 xor 0 = 1 a = a^b = 1 xor 1 = 0

    Even though you initially overwrite the value of a, no information is lost; its encoding simply changes. In the case of larger (multi-bit) numbers, each pair of bits will be swapped in the same way, and so the entire int values are swapped.

    However that is unlikely to be any use to you at all ;-) as I don't see an *easy* way to implement this in SQL (you might XOR into a collision with an existing ID value) so I would probably just do it with a temp value (must not exist in the table already!) so you free up the id value.

    mysql> select * from test; +------+------+ | id | num | +------+------+ | 1 | 100 | | 2 | 200 | +------+------+ 2 rows in set (0.00 sec) mysql> update test set id=666 where id=1; update test set id=1 where i +d=2; update test set id=2 where id=666; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; +------+------+ | id | num | +------+------+ | 2 | 100 | | 1 | 200 | +------+------+ 2 rows in set (0.00 sec) mysql>

    cheers

    tachyon

Re: non-perl SQL question
by dbwiz (Curate) on Dec 31, 2003 at 12:55 UTC

    Here is a pure SQL solution, which works only if the column being swapped is not a unique key.

    mysql> select * from swaptable; +----+------+ | id | num | +----+------+ | 5 | 100 | | 6 | 200 | | 7 | 300 | +----+------+ 3 rows in set (0.00 sec) mysql> UPDATE swaptable SET id = CASE id WHEN 5 THEN 6 -> WHEN 6 THEN 5 END -> WHERE id IN (5,6); Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from swaptable; +----+------+ | id | num | +----+------+ | 6 | 100 | | 5 | 200 | | 7 | 300 | +----+------+ 3 rows in set (0.00 sec) mysql> UPDATE swaptable SET id = CASE id WHEN 5 THEN 6 -> WHEN 6 THEN 5 END -> WHERE id IN (5,6); Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from swaptable; +----+------+ | id | num | +----+------+ | 5 | 100 | | 6 | 200 | | 7 | 300 | +----+------+ 3 rows in set (0.00 sec)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://317829]
Approved by Roger
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (4)
As of 2019-12-12 11:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?