Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail
From: Thomas 'PointedEars' Lahn <PointedEars@web.de>
Newsgroups: comp.lang.php
Subject: Re: sql how capture on the fly the row's id when is updated
Date: Sun, 02 Aug 2015 14:42:13 +0200
Organization: PointedEars Software (PES)
Lines: 45
Message-ID: <2899955.DplpsR2aGK@PointedEars.de>
References: <mpi2nt$gpp$1@speranza.aioe.org> <87a8uaokt1.fsf@ahungry.com>
Reply-To: Thomas 'PointedEars' Lahn <php@PointedEars.de>
Mime-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8Bit
X-Trace: solani.org 1438519529 17445 eJwFwYERADEIArCZUKF2nOK9+4/wCVPQnBJVXG6cnXxW4iZaHWpYUVqfr2wQserkK2vcPwafEHQ= (2 Aug 2015 12:45:29 GMT)
X-Complaints-To: abuse@news.solani.org
NNTP-Posting-Date: Sun, 2 Aug 2015 12:45:29 +0000 (UTC)
User-Agent: KNode/4.14.2
X-User-ID: eJwNyLkBACAIBLCVAHnHEeX2H0GbFLHl7CfUzdVg2CiwsN44TUs1sbPaGCI522sc1Z8Rpn/EPRpyhAIR1+YBWqUVmQ==
Cancel-Lock: sha1:5wQEtW8H4iEMfWQFwm7zFsMBrak=
X-NNTP-Posting-Host: eJwFwYERgDAMAsCV2kugOI6hsv8I/qO46dMEG0EmefjK7ONdyHcs1Cz13GtWnF42ZWUU/TFEEg4=
Xref: csiph.com comp.lang.php:15626

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.