Return last updated ID

I am performing an update to a row dependent on a condition and need to return the ID of the row affected.

This is what I have tried

$update =   "UPDATE table_1
SET delete_flag = 1,
id = LAST_INSERT_ID(id)
WHERE other_id = $otherId;
SELECT LAST_INSERT_ID();";

$result = $this->db->query($update);

$result returns nothing

$update =   "UPDATE table_1
SET delete_flag = 1
WHERE other_id = $otherId;";

$result = $this->db->query($update);
$id = $this->db->lastInsertId();

$id returns nothing

What am I missing?

Have you tried using execute instead of query?

$update =   "UPDATE table_1
SET delete_flag = 1
WHERE other_id = $otherId;";

$result = $this->db->execute($update);
$id = $this->db->lastInsertId();


8.0k

Hi,

Unfortunately $id returns 0;

It works fine for inserts.

Tim



89.5k

Update statements does not return a "last insert id", database drivers only return it for inserts



8.0k

Sure, but it is possible and very useful to avoid unncessary queries.

Here are a few strategies:

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

But I guess you've answered my question... It is not possible to do using Phalcon.



89.5k

No, it's not possible with PDO



8.0k

Very helpful!



4.9k

Hello! I'm wondering about which is the ODM way to do what the OP does.