Getting a list of updated rows?

If I run an UPDATE statement, is there any statement I can then run
which will return a list of the primary keys of all the rows updated
in the previous query? Or can that only be done by running a `SELECT
id` (etc.) with the same WHERE conditions before doing the UPDATE?

On 8 Sep, 16:06, Adam Atlas <a...@atlas.st> wrote:
> If I run an UPDATE statement, is there any statement I can then run
> which will return a list of the primary keys of all the rows updated
> in the previous query? Or can that only be done by running a `SELECT
> id` (etc.) with the same WHERE conditions before doing the UPDATE?

Why not have a 'last_updated' column with a timestamp. Then you can
just select all rows updated after the query was executed.

strawberry wrote:

> On 8 Sep, 16:06, Adam Atlas <a...@atlas.st> wrote:
>> If I run an UPDATE statement, is there any statement I can then run
>> which will return a list of the primary keys of all the rows updated
>> in the previous query? Or can that only be done by running a `SELECT
>> id` (etc.) with the same WHERE conditions before doing the UPDATE?
>
> Why not have a 'last_updated' column with a timestamp. Then you can
> just select all rows updated after the query was executed.


That would not work because other processes could make update before you
executed that query.


--
Brian Wakem

On 8 Sep, 20:30, Brian Wakem <n...@email.com> wrote:
> strawberry wrote:
> > On 8 Sep, 16:06, Adam Atlas <a...@atlas.st> wrote:
> >> If I run an UPDATE statement, is there any statement I can then run
> >> which will return a list of the primary keys of all the rows updated
> >> in the previous query? Or can that only be done by running a `SELECT
> >> id` (etc.) with the same WHERE conditions before doing the UPDATE?
>
> > Why not have a 'last_updated' column with a timestamp. Then you can
> > just select all rows updated after the query was executed.
>
> That would not work because other processes could make update before you
> executed that query.
>
> --
> Brian Wakem

Hey, if you've got a better solution, share it with us. I'm here to
learn too.

strawberry wrote:

> On 8 Sep, 20:30, Brian Wakem <n...@email.com> wrote:
>> strawberry wrote:
>> > On 8 Sep, 16:06, Adam Atlas <a...@atlas.st> wrote:
>> >> If I run an UPDATE statement, is there any statement I can then run
>> >> which will return a list of the primary keys of all the rows updated
>> >> in the previous query? Or can that only be done by running a `SELECT
>> >> id` (etc.) with the same WHERE conditions before doing the UPDATE?
>>
>> > Why not have a 'last_updated' column with a timestamp. Then you can
>> > just select all rows updated after the query was executed.
>>
>> That would not work because other processes could make update before you
>> executed that query.
>>
>> --
>> Brian Wakem
>
> Hey, if you've got a better solution, share it with us. I'm here to
> learn too.


The only way I can think of is to LOCK the table(s) first, then SELECT using
the same WHERE clause you intend to use in the UPDATE.

Then UPDATE and UNLOCK.


--
Brian Wakem

If you are updating on the primary keys, then you already know what
those are. If you are updating on a secondary key or non-key field,
you could do a query returning the primary keys associated with the
field you are using to select records for update.

One issue might arise if you are sending a query that does not contain
data to for some records and want to know primary keys associated only
with the records for which you are sending data, i.e. those actually
updated. In this case, you could test your update data in php, see
which key fields your script was sending data for and proceed as
described above.

Seems pretty straightforward, or am I missing something?

--Kenoli

On Sep 8, 8:06 am, Adam Atlas <a...@atlas.st> wrote:
> If I run an UPDATE statement, is there any statement I can then run
> which will return a list of the primary keys of all the rows updated
> in the previous query? Or can that only be done by running a `SELECT
> id` (etc.) with the same WHERE conditions before doing the UPDATE?