Select Statement With An Update

Question

I am creating a site that displays articles. The article table has the following fields:

article_id
title
text
views

ATM I am selecting the the article and outputting it but I would like to also increment the views in the same query ie "views=views+1".

The query would look like this

SELECT article_id, title, text, views
SET views=views+1
WHERE article_id=:article_id

I want to get the incremented result of views as well in the output so I am guessing I would need place the SET before the SELECT.

Duplicate of http://stackoverflow.com/questions/562693/mysql-update-and-select-in-one-pass ? — Manjula, Jan 24 '13 at 17:18

score 1 · Accepted Answer · answered Jan 24 '13 at 17:07

It would have to be two separate queries.

SELECT 
    article_id, 
    title, 
    text, 
    views
FROM TABLE
WHERE article_id = article_id;

UPDATE TABLE
SET views = views + 1
WHERE article_id = article_id;

You could wrap those in a transaction to force both to either happen or rollback.

START TRANSACTION;

SELECT 
    article_id, 
    title, 
    text, 
    views
FROM TABLE
WHERE article_id = article_id;

UPDATE TABLE
SET views = views + 1
WHERE article_id = article_id;

COMMIT;

Select Statement With An Update

1 Answers1