Update and Select in the same query in MySQL

Question

I would like to update and count in a single query.

My table structure is:

|----|-------|------------|
| id | views | update     |
|----|-------|------------|
|  1 |     1 | 2019-05-30 |
|----|-------|------------|

The desired output is:

|----|-------|------------|
| id | views | update     |
|----|-------|------------|
|  1 |     2 | 2019-05-31 |
|----|-------|------------|

My try is the following:

UPDATE table 
SET views = (
    SELECT COUNT(views) 
    FROM table 
    WHERE id='1' 
) + 1, update='2019-05-31' WHERE id='1';

I'm tried to update the view count by adding +1 to the actual count and update the update field with the new date.

But it doesn't work.

What I'm doing wrong ?

Thanks.

Answer 1

Just do this like.

UPDATE table SET views = views + 1, update='2019-05-31' WHERE id='1';