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;

Multiple rows solution works great, just want to mention that null is not a recognised value for user defined variables in MySQL. So you might need to assign a value and ignore it. '0' would be ideal as it doesn't refer to an actual id in your table. This would make line 9 in your code above.

SET @uids := 0;

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'.

I tried to update multiple rows and their user_ids, here user_id is an email address (i.e., a string). The updating actually didn't happen, that is, the status were not set to 1.
SET @uids := null;
update user set status = 1 where last_update_time <= (now() - interval 5 second) and ( SELECT @uids := CONCAT_WS(',', user_id, @uids) );
select @uids;

However, @uids indeed returned a list of the email addresses with @uids's type as binary.

I tried to update multiple rows and their user_ids, here user_id is an email address (i.e., a string). The updating actually didn't happen, that is, the status were not set to 1.
SET @uids := null;
update user set status = 1 where last_update_time <= (now() - interval 5 second) and ( SELECT @uids := CONCAT_WS(',', user_id, @uids) );
select @uids;

However, @uids indeed returned a list of the email addresses with @uids's type as binary.

If anyone else has the problem of @uids being binary try this.

SELECT CONVERT(@uids USING utf8mb4) as 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))