MySQL is it possible to select and set at same time?

Question

I have two queries. One selects the rows, and other one sets the row values;

mysql_query("SELECT * from messages where user='12'");

mysql_query("UPDATE messages set read='yes' where user='12'");

Is it possible to make it work using one query using if statement ? if read row is not equals to yes, update ?

You don't need to select rows first in order to update them. — RevanProdigalKnight, Jan 08 '16 at 18:57
I know. But its required because the values are being echoed. — user198989, Jan 08 '16 at 18:58
In that case, I don't think it's possible to do it in one statement. — RevanProdigalKnight, Jan 08 '16 at 18:59
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jan 08 '16 at 19:34

score 1 · Accepted Answer · edited May 23 '17 at 10:28

$result = mysql_query("SELECT * from messages where user='12'");

If (!result)
{
   //Do something
}
else
{
   mysql_query("UPDATE messages set read='yes' where user='12'");
}

Also see, how to check if mysql query return no result(record not found) using php?

EDIT: Your question is unclear as to what you are asking, but it seems to say how can you do what you want with an if statement, not do both in one query, which, if that is the case, I do not know what that question means.

EDIT: After thinking, try this,

IF EXISTS (SELECT * from messages where user='12')
THEN
UPDATE messages set read='yes' where user='12'
ELSE
SELECT * from messages where user='12'

(or do an insert)

Cue taken from,

SQL - IF EXISTS UPDATE ELSE INSERT Syntax Error

@user198989 if it works, don't forget to check it as the answer :). — johnny, Jan 08 '16 at 19:20

score 1 · Answer 2 · answered Jan 08 '16 at 19:01

There might be a way to do this with stored procedures, but in general, no, retrieval and updating are distinct operations in SQL.

If what you are concerned about is making sure that only the messages you read are updated (in the case that new messages are added to the system between the select and update calls), then you will need to specify the specific message ids to update in the where clause of your update statement.

MySQL is it possible to select and set at same time?

2 Answers2