UPDATE/DELETE in mysql and get the list of affected row ids?

Question

Is there an efficient way to get the list of affected row IDs (not the # of affected rows via PHP's mysql_affected_rows(), but the actual row ids that were affected) from an UPDATE or DELETE query in mysql?

In postgresql, there is a RETURNING clause within UPDATE/DELETE queries that can be used to specify values from the affected rows that are returned.

In mysql, the 'brute force' way of getting the affected rows seem to be: 1. Acquire READ LOCK. 2. SELECT to with the WHERE condition of the UPDATE/DELETE query to get the affected row ids. 3. UPDATE/DELETE. 4. RELEASE LOCK.

The above way seems very inefficient. Is there a more efficient way to get the affected row ids in mysql?

Amro · Answer 1 · 2012-02-24T12:48:33.007

You can create a Trigger

Support for triggers is included beginning with MySQL 5.0.2. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.

the following code creates a trigger on a table named mytable which has a field id

CREATE TRIGGER mytable_delete
AFTER DELETE ON mytable
FOR EACH ROW SET @deletedIDs = CONCAT_WS(',', @deletedIDs, OLD.id)

notice that OLD refers to deleted row

once you created a trigger on a table you can use it as follows:

/* empty parameter defined in CREATE TRIGGER */
Set @deletedIDs = '';
/* perform your query */
DELETE FROM mytable WHERE myotherfield = 'myfilterevalue';
/* get the parameter */
SELECT @deletedIDs AS 'Deleted_IDs';

this will return deleted IDs each preceded by a comma in a string

sql mysql php

I suggest to use this trigger. `CREATE TRIGGER mytable_delete AFTER DELETE ON mytable FOR EACH ROW SET @deletedIDs = TRIM(LEADING ',' FROM CONCAT_WS(',', @deletedIDs, OLD.id))` This removes the leading comma. — Sirio, Jan 23 '17 at 14:52

score 10 · Answer 2 · answered Jan 15 '12 at 22:34

10

try this, it will return the updated ids as "1,2,3....":

SET @uids := '';
UPDATE table_name
   SET some_col= 'some_val'
 WHERE some_col= 'some_val'
   AND ( SELECT @uids := CONCAT_WS(',', @uids, id) );
SELECT TRIM(LEADING ',' FROM @uids);

answered Jan 15 '12 at 22:34

redmoon7777

4,498
1
24
26

You should open a new question about that, because it is a different case. You also should include the query you used and the errors that were reported. (if you do, provide me with the link to the new question and I'll be happy to help) – redmoon7777 Feb 19 '14 at 22:01
I solved it, was typing my comment and example and the editor didn't allow me to get a new line, so nevermind. – Dan Feb 19 '14 at 22:06

score 0 · Answer 3 · answered Jul 19 '22 at 12:37

Using @redmoon7777 answer, it gets the updated ids and Concat them to uids, but I found out the columns are not updated.

Maybe at the time of his answer, it's updating, but it's more than 8 years ago so I believe it might work on old MySQL versions.

But using it on version 5.7.14 and maybe higher, won't update.

So similar to his solution, I added extra condition to make the columns update

SET @uids := '';
UPDATE table_name SET some_col= 'some_val' WHERE some_col= 'some_val' AND ( SELECT @uids := CONCAT_WS(',', @uids, id)) != '';
SELECT TRIM(LEADING ',' FROM @uids);

UPDATE/DELETE in mysql and get the list of affected row ids?

3 Answers3

Linked

Related