CODEWITHSUNDEEP
mysql
Max
Max

Reputation: 259

How to update all the results after a SELECT query in MySQL

After I performed a SELECT query I use to show all the results on my page, I wish to to UPDATE all the shots.views int column with +1 , which means everytime it shows these results it also updates the shots.views of it.

How do you guys think I should do this the best way and a little example? Of course I want to limit the amount of query's. I'm a little bit clueless right now. Thanks in advance! 

$query = mysql_query("
SELECT 
shots.id,
shots.large, 
shots.title,
shots.datetime 

FROM notifications

INNER JOIN shots

ON notifications.target = shots.id

WHERE notifications.uid = (SELECT id FROM users WHERE username ='$username') AND (notifications.type = 'picture' OR notifications.type = 'video' OR notifications.type = 'reshot')

ORDER BY datetime DESC

") or die(mysql_error());

Upvotes: 0

Views: 453

Answers (2)

Bowersbros
Bowersbros

Reputation: 3598

SELECT value FROM counters WHERE id = 1 FOR UPDATE;
UPDATE counters SET value = value + 1 WHERE id = 1;

Thats what you're looking for :)

Upvotes: 0

Alain Collins
Alain Collins

Reputation: 16362

Since UPDATE doesn't really return anything, I think you're stick to using two queries. It was a noble goal, though!

Upvotes: 1

Related Questions