CODEWITHSUNDEEP
mysqlsql
Nuvolari
Nuvolari

Reputation: 1155

Select Statement With An Update

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.

Upvotes: 0

Views: 61

Answers (1)

Dustin Laine
Dustin Laine

Reputation: 38503

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;

Upvotes: 1

Related Questions