Search code examples
mysqlselectsql-update

Mysql: First Select then Update in One Query

I need to select the rows of a table then update the field of the rows. For example:

"SELECT * FROM `messages` WHERE `timestamp`>'1422680952'"

Then I want to update a field of that rows.

Solution

  • The operation you describe, returning a resultset from a SELECT and performing an UPDATE cannot be performed in a single SQL statement in MySQL.

    You would need two separate SQL statements: a SELECT and an UPDATE.

    In terms of MySQL, it is not necessary to run a SELECT before running an UPDATE, it's possible to run just an UPDATE.

    UPDATE `messages` SET somecol = 'somevalue' WHERE `timestamp`>'1422680952'

    (The query in the question is enclosed in double quotes. That leads us to suspect that you are running this statement from a database interface library in a language such as PHP.)

    It is possible to get multiple statements to execute as part of a single transaction. But as far as how the MySQL server itself is actually processing the specified operations, that's going to be two separate statements.