Geek Answers Handbook

Is there any way MySQL can update and select the updated rows in a single t expression?

Can I join a SELECT and UPDATE statement? If so, how can I do this?

I have a table whose name is news .

I display news on my website. If someone reads the news, I add 1 to the hit . I am doing this with two queries:

mysql_query("SELECT * FROM news WHERE id=2 LIMIT 1");
mysql_query("UPDATE news SET hit=hit+1 WHERE id=2");

But I want to join them.

id | title     | content           |hit
---+-----------+-------------------+---
1  | sdfsdfdsf | dfsdffdsfds836173 |5
2  | sfsdfds   | sdfsdsdfdsfdsfsd4 |9
3  | ssdfsdfds | sdfdsfs           |3

Update: OP wants to update and select the updated rows in a single SQL statement.

+5
source share
3 answers

Write a stored procedure:

delimiter //;
DROP PROCEDURE IF EXISTS ReadNews//;
CREATE PROCEDURE ReadNews(IN newsId INT)
BEGIN
    SELECT * FROM news WHERE id=newsId LIMIT 1;
    UPDATE news SET hit=hit+1 WHERE id=newsId;
END

Using:

CALL ReadNews(2)

Update

, mysql . ( Cse #)

var result = DB.Execute("INSERT INTO table (id, name) VALUES (1, 'test'); SELECT LAST_INSERT_ID()");

.

,

var result = mysql_query("UPDATE news SET hit=hit+1 WHERE id=2; SELECT * FROM news WHERE id = 2");
+1

(tinyint, int, float ..).

mysql_query("UPDATE news SET hit=hit+1 WHERE id=2 ");

, .

0

The best way is to record a trigger that will increase the number of hits on selected news. When SELECT * FROM news WHERE id = 2 LIMIT 1 Fire trigger UPDATE news SET hit = hit + 1 WHERE id = 2

0
source

More articles:


All Articles