Any way to transfer value from one cell to another?

Question

Is there any way in the SQL language or in MySQL (or other DBMA) to transfer a value from one cell to another? For example, say there is a table called user_cars with the following structure:

|id| |user_name| |num_cars|

Bob has 5 cars, and John has 3 cars. Is there any way to in one query subtract 2 cars from Bob and add 2 to John? I know this can be done with two update queries, but I'd just like to know if there was a more efficient way.

Answer 1

That's what transactions are for ...

Answer 2

If you really want to do it in one query, you can do an update on a self join of the table, but it's both less readable and probably less efficient.

Answer 3

For Oracle you could do this. Don't know if there is an equivalent in mysql. Obviously this particular statement is very specific to the example you stated.

 UPDATE user_cars
   SET num_cars = num_cars +
                     CASE WHEN user_name='Bob' THEN -2
                          WHEN user_name='John' THEN +2
                     END
   WHERE user_name IN ( 'Bob', 'John' )

Answer 4

This will work, but it is not pleasant:

UPDATE USER_CARS UC
SET
  NUM_CARS = NUM_CARS + CASE WHEN UC.USER_NAME = 'Bob'
                             THEN -2  --take from bob
                             WHEN UC.USER_NAME = 'John'
                             THEN 2  --give to John
                             ELSE 0  --no change for anybody else
                        END

Answer 5

I agree with Jonas Klemming, there isn´t a meaningful way of doing that. This is precisely what transactions were invented to do.

Answer 6

As others have explained, you can do it with a CASE statement.
However, doing so is probably unwise, as it makes the intention of the code harder to see. This will make it harder for a subsequent programmer to understand the purpose of the code.

Unless you have some specific reason for needing to do it in one statement, the best thing to do is to use 2 update statements, wrapping them in a transaction if required.

I often recall this quotation (although I had to look up the attribution on wikiquote):

"Programs must be written for people to read, and only incidentally for machines to execute." - Abelson & Sussman, SICP, preface to the first edition