mysql更新返回,mysql从更新返回结果

I want to select a bunch of rows from a mysql database and update the viewed attribute of those once selected (this is a kind of 'I have read these' flag).

Initially I did something like this:

update (

select a, b, c

from mytable

where viewed = '0'

)

set viewed = '1';

This selects the rows nicely and updates their viewed attribute as required. But it does not return the selected rows from the subquery.

Is there a clause I can add, or perhaps I need to store the subquery, etc...? I did consider a transaction but I ended up with the same problem. I have not tried a stored procedure...

Please can someone advise / point me in the right direction on how to do what I do above but in addition return the selected tables from the subquery?

Thanks in advance.

Update:

As pointed out by @Barmar, @a_horse_with_no_name, @fancyPants and @George Garchagudashvil...

In MySQL you have to use two statements to select and update, and not a nested statement as in my initial post, if you want to return the selected rows.

e.g.

begin;

select a, b, c

from mytable

where viewed = '0';

update mytable

set viewed = '1'

where viewed = '0';

commit;

thanks guys.

解决方案

I would create a simple function:

DELIMITER $$

DROP FUNCTION IF EXISTS `mydb`.`updateMytable`$$

CREATE

/*[DEFINER = { user | CURRENT_USER }]*/

FUNCTION `mydb`.`updateMytable`() RETURNS TEXT

BEGIN

SET @updated := '';

UPDATE mytable

SET viewed = 1

WHERE viewed = 0

AND (

SELECT @updated := CONCAT_WS(',', @updated, id)

) != ''

;

RETURN TRIM(LEADING ',' FROM @updated);

END$$

DELIMITER ;

which updates tables and returns concatenated ids.

From php you call this:

SELECT mydb.updateMytable()

and you get ids in a stirng: 1,2,7,54,132 etc...

Update:

my function is returning string containing comma separated ids:

'1,5,7,52,...' these ids are only which would have been updated during the function call,

better php-mysql example would be (you may and would use PDO):

$query = "SELECT mydb.updateMytable()";

$res = mysql_query($query);

$arr = mysql_fetch_array($res);

$ids = explode(',', $arr[0]);

// now you can do whatever you want to do with ids

foreach ($ids as $id)

{

echo "Hoorah: updated $id\n";

}

also remember to change mydb and mytable according to your database names

Final

because you need more complex functionality, simply run two query:

First run:

SELECT a, b, c

FROM mytable

WHERE viewed = 0

Next run:

UPDATE mytable

SET viewed = 1

WHERE viewed = 0