How to get the last updated row using a single query

Question

I searched enough before posting it.

My table structure:

aid | bid | cid | did |

Where aid, bid together are the primary keys.

When I update the value of cid using a where clause for aid, bid I also want to get the did value of the updated row.

Something like this:

$this->db->set('cid', 1, FALSE)
        ->where(array(
                    'aid'   => $a_id,
                    'bid'   => $b_id
                ))
        ->update('my_table')
        ->select('did');

The above query says:

Fatal error: Call to a member function select() on a non-object in...

I tried this:

How to get ID of the last updated row in MySQL?

Which is like 3 queries.

Why is it a problem to use more queries? – Bart Friederichs Aug 05 '13 at 07:10 — Bart Friederichs, Aug 05 '13 at 07:10

score 0 · Accepted Answer · answered Aug 05 '13 at 07:10

I'd suggest fetching the values you're about to update, store their IDs in an array, and run an UPDATE with a WHERE id IN (1, 2, ...).

What you're trying to do is not supported by MySQL. You'll need to run at least 2 queries, and since you're fetching the values the first time and already know what values you're updating, then you can also recreate the new row and it's values without using a query after UPDATE.

In your given example:

$this->db->set('cid', 1, FALSE)
        ->where(array(
                    'aid'   => $a_id,
                    'bid'   => $b_id
                ))
        ->update('my_table')
        ->select('did');

set(), where() and also select() returns an object that builds on the query. However update() return a value which is the results and doesn't have a function called select() and not set() and where() for that matter.

How to get the last updated row using a single query

1 Answers1