CODEWITHSUNDEEP
mysqlselectsql-update
Jajarm
Jajarm

Reputation: 41

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.

Upvotes: 0

Views: 16412

Answers (5)

eLemEnt
eLemEnt

Reputation: 1801

You can use Transaction for this purpose where you can have both select and update inside Transaction

START TRANSACTION;
 
  -- Let's get the current value
  SELECT value FROM counters WHERE id = 1 FOR UPDATE;
 
   -- Increment the counter
  UPDATE counters SET value = value + 1 WHERE id = 1;
 
  COMMIT;

Generally it is done with this way

try {
    /* First of all, let's begin a transaction */
    $db->beginTransaction();

    /* A set of queries; if one fails, an exception should be thrown */
    $db->query('select query');
    $db->query('update query');

    /* If we arrive here, it means that no exception was thrown */
    /* i.e. no query has failed, and we can commit the transaction */
    $db->commit();
} catch (Exception $e) {
    /* An exception has been thrown */
    /* We must rollback the transaction */
    $db->rollback();
}

Upvotes: 0

maswerdna
maswerdna

Reputation: 315

CAVEAT: This code is not presented as an answer or as a working code. But to further explain the concept of the question.

I think what the OP wants is something like this -

"SELECT *, (UPDATE messages SET display = display + 1 WHERE key = key) AS display FROM messages WHERE timestamp > '1422680952'"

This is possible with SELECT as the sub query, but I can't say if it's possible with UPDATE.

I came across this question when I ran into a similar case in my application. I needed to store the number of views, i.e. number of database hits on a table.row without writing 2 queries.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

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.

Upvotes: 3

SMA
SMA

Reputation: 37023

For updating the row, you don't necessarily have to select the rows. Try something like:

UPDATE messages
SET display = 1
WHERE timestamp >'1422680952'

Upvotes: 1

Polynomial Proton
Polynomial Proton

Reputation: 5135

You can use the syntax below, if you purpose is just updating the table:

Update messages
Set display = 1
Where timestamp >'1422680952'

Upvotes: -1

Related Questions