-
-
Save PieterScheffers/189cad9510d304118c33135965e9cddb to your computer and use it in GitHub Desktop.
# 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; |
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))
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;