MySQL - Get id of updated rows

mysql_get_last_updated_id.sql

# http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql

# single row update
SET @update_id := 0;
UPDATE some_table SET column_name = 'value', id = (SELECT @update_id := id)
WHERE some_other_column = 'blah' LIMIT 1; 
SELECT @update_id;

# Multiple rows updated
SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

Hi, has anyone tried UPDATE with INNER JOIN?

When I am updating multiple records, I want to get updated rows id, but I am facing some issues on that.

I am having one table with three columns.

> CREATE TABLE user (
>   id int(11) NOT NULL AUTO_INCREMENT,
>   name varchar(45) DEFAULT NULL,
>   status tinyint(4) DEFAULT NULL,
>   PRIMARY KEY (id)
> )

and I am having three records in a user table.

> id  name  status
>  1  akash   1
>  2  ravi    1
>  3  rakesh  1  

Now, I want to update status to 0 to all rows. So I want to get result like 1,2,3 because all three rows will get affected.

I have also written one store procedure for that.

> CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`()
> BEGIN
> SET @uids := 0;
> UPDATE user
>    SET status = 0
>  WHERE status = 1
>    AND ( SELECT @uids := CONCAT_WS(',', id, @uids) );
> SELECT @uids;
> END

When I call this procedure, I got an error like Error Code: 1292. Truncated incorrect DOUBLE value: '1,0'.

use can set SELECT find_in_set(id,@uids := CONCAT_WS(',', id, @uids))