Update inside CASE (MySQL)

Question

i've got two queries first:

SELECT
    players.username AS username,
    tmp_player_operations.id AS tmp_id, 
    tmp_player_operations.operation_type AS operation_type, 
    tmp_player_operations.veryfication_code AS veryfication_code, 
    tmp_player_operations.expiration_date AS expiration_date,
    tmp_player_operations.used AS used
FROM
    players LEFT OUTER JOIN tmp_player_operations 
    ON players.id = tmp_player_operations.player_id
WHERE
tmp_player_operations.veryfication_code = '3c3c4375086fbcbc1cef938a0bfabbb9'

second:

UPDATE 
    players LEFT JOIN tmp_player_operations 
    ON players.id = tmp_player_operations.player_id
SET 
    tmp_player_operations.used = 1,
    players.active = 1
WHERE
    tmp_player_operations.id = 8

Now i would like to make this update inside select, i mean something like:

(CASE
   WHEN
      tmp_player_operations.used = 0
      THEN
          UPDATE....
          AND 'updated'
   ELSE
      'not updated'
END) AS something

is something like this possible?

derobert · Accepted Answer · 2009-08-15 08:01:10Z

1

No, it's not possible. A query can be either a SELECT or an UPDATE, but not both.

You could, I suppose, use a stored procedure to make one database call to do both the update and the select, but that'd still really be two queries (but that application would only see one, the stored proc call). Chapter 18 of the MySQL manual has details on stored procedures.

Perhaps you should back up a bit, and ask us how to accomplish whatever your actual goal is?

answered Aug 15, 2009 at 8:01

derobert

51.4k15 gold badges98 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cfaniak Over a year ago

I'll make it with stored proc, i only wanted to know if it is possible, so your answer helped me. I just wanted to make one mysql connection instead of two.

derobert Over a year ago

You can issue multiple queries per connection, not sure why you'd need two connections.

Andrius Naruševičius · Accepted Answer · 2012-05-05 19:43:16Z

0

Update an additional field with a value 1. Then run one more query and select "Updated" where that field is equal to one.

answered May 5, 2012 at 19:43

Andrius Naruševičius

8,6289 gold badges53 silver badges83 bronze badges

Collectives™ on Stack Overflow

Update inside CASE (MySQL)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related