Getting all effected rown in MySQL UPDATE

Question

Title pretty much says it. I am using the deprecated mysql_ functions. I've got a query like:

UPDATE `table`
SET `row` = 'newtext'
WHERE `row` = 'oldtext'

Is there an easy way to get all effected row's primary key? Some glorious combination of mysql_insert_id and mysql_affected_rows? How can I do this without looping and doing each update one row at a time?

I think I'm gonna have to. Just wanted to check for an easier solution first. — I wrestled a bear once., Dec 22 '13 at 14:49
http://stackoverflow.com/questions/8873805/update-delete-in-mysql-and-get-the-list-of-affected-row-ids — Nouphal.M, Dec 22 '13 at 14:50
@Adelphia . . . The first thing that comes to my mind is a trigger. — Gordon Linoff, Dec 22 '13 at 14:50
Unless the rows are being updated so quickly that you cannot lock the tables for writing, I think the simplest approach would just be to `SELECT` the primary keys using the same `WHERE` condition and *then* do the same update. — Explosion Pills, Dec 22 '13 at 14:52
@ExplosionPills . . . Introducing race conditions *on purpose* into an application seems like a really, really bad idea. How about doing the two operations inside a single transaction? — Gordon Linoff, Dec 22 '13 at 15:43
@GordonLinoff that's what I was referring to with respect to locking the tables. You would have to lock it for writing before doing the `SELECT`. — Explosion Pills, Dec 22 '13 at 15:43

score 1 · Accepted Answer · answered Dec 22 '13 at 17:07

1

As a solution to your problem please refer the following sample code snippet

  UPDATE `table` SET `row` = 'newtext' WHERE `row` = 'oldtext'

  select id from table where row='oldtext'

answered Dec 22 '13 at 17:07

Rubin Porwal

3,736
1
23
26

Getting all effected rown in MySQL UPDATE

1 Answers1