CODEWITHSUNDEEP
phpmysqlsql
w3father
w3father

Reputation: 569

How to update column while selecting row?

I have a videos table.

I want to select a row in table at the same time i want to update views(+1) column in same row.

Is here anything possible in a single query?

Upvotes: 3

Views: 444

Answers (4)

Hamid
Hamid

Reputation: 1760

If You want to do this in a query , you can create trigger on SELECT Like this :

CREATE TRIGGER [TRIGGER NAME] AFTER SELECT ON [TABLE NAME]
  FOR EACH ROW BEGIN
    UPDATE [TABLE NAME] SET [ FIELD ] = [ FIELD ] + 1 WHERE ...... ;
END;

Each time you select from your table , foreach result that matched by your where clause result fields will update

Upvotes: 1

Jonathan Hall
Jonathan Hall

Reputation: 79546

PostgreSQL supports UPDATE ... RETURNING, which would do what you want, but I don't believe MySQL can do this. You will need to do two queries.

Upvotes: -1

hanguofeng
hanguofeng

Reputation: 49

how single the queryis? SELECT * FROM test_table WHERE id=1 ;update test_table set test_col=test_col+1 where id=1;

Upvotes: 1

Mat
Mat

Reputation: 206679

No, you'll have to do that with two queries.

Upvotes: 3

Related Questions