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();


4.7k

Hi,

Unfortunately $id returns 0;

It works fine for inserts.

Tim



83.4k

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



4.7k

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.



83.4k

No, it's not possible with PDO



4.7k

Very helpful!