Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #15626

Re: sql how capture on the fly the row's id when is updated

Show key headers only | View raw

Matthew Carter wrote:

> It's not the best choice to do it as phrased, but to do so you would
> (memory permitting) query all the rows/relevant fields from the
> database, then perform your update query, then re-query all the
> rows/relevant fields into a new array.
> 
> Loop through the two arrays and when the field you're checking is
> different, do something with it.

Of all the possible approaches, this is probably the second to worst one.

Of the ones that come to mind, the most compatible and efficient one appears 
to be to insert at least one new column (not necessarily in the same table), 
update that as well –

  UPDATE
    a INNER JOIN c ON a.id = c.id
    SET a.total=1, a.updated=1
    WHERE b.x < c.y  

  (I have removed pointless aliases)

–, and then you perform a second query, querying only the IDs of the
rows that have a “1” in that new column.

In fact, you might not need to create a new column as there may already be a 
column that stores the date of the last update, or a counter, against which 
you can compare.  For example, in MySQL it could be a column with type 
TIMESTAMP, default value CURRENT_TIMESTAMP, and trigger ON UPDATE 
CURRENT_TIMESTAMP.  Such a column frequently comes in handy, so I prefer to 
create it with the table.

It is possible that the `total` column already can serve that purpose, in 
which case you only need to do

 UPDATE a SET a.total=0

beforehand, and then proceed with the now-third query as I described.

-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-01 11:15 +0200
  Re: sql how capture on the fly the row's id when is updated Denis McMahon <denismfmcmahon@gmail.com> - 2015-08-01 14:51 +0000
  Re: sql how capture on the fly the row's id when is updated "Peter H. Coffin" <hellsop@ninehells.com> - 2015-08-01 10:03 -0500
  Re: sql how capture on the fly the row's id when is updated Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-08-01 20:33 +0200
  Re: sql how capture on the fly the row's id when is updated Matthew Carter <m@ahungry.com> - 2015-08-01 15:44 -0400
    Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-01 22:01 +0200
      Re: sql how capture on the fly the row's id when is updated Matthew Carter <m@ahungry.com> - 2015-08-01 16:13 -0400
        Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-02 01:51 +0200
          Re: sql how capture on the fly the row's id when is updated Matthew Carter <m@ahungry.com> - 2015-08-01 21:38 -0400
    Re: sql how capture on the fly the row's id when is updated Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-08-02 14:42 +0200
      Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-04 19:24 +0200
        Re: sql how capture on the fly the row's id when is updated Matthew Carter <m@ahungry.com> - 2015-08-04 14:55 -0400
          Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-05 01:13 +0200
        Re: sql how capture on the fly the row's id when is updated Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-08-04 21:29 +0200
          Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-05 01:01 +0200
            Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-05 01:03 +0200
            Address munging considered harmful (was: sql how capture on the fly the row's id when is updated) Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-08-05 10:08 +0200

csiph-web