NYCPHP Meetup

[Tim Gales]

To: 'Brian Pang'
Subject: RE: [nycphp-talk] mySQL: update table from another
table


You wrote:
...Thanks, but this gives me an error

Brian, sorry about speaking 'Dervish' (as in whirling
dervish)in my earlier post. I am often reluctant to say
things in a definitive way E.G. "It can't be done" even with
an AFAIK on it.

So I tend to just quote something definitive. 

The active ingredient in my prior post was:
>  "  * Updates and deletes that run over multiple tables is
harder to
> do
>      in MySQL. This will, however, be fixed in MySQL 4.0
> with multi-table
>      `DELETE' and multi-table `UPDATE' and in MySQL 4.1
with 
> subselects. "

When the manual says "harder to do" I take that to mean
(waffling again) the developer has to handle it
programatically.

I quoted the manual so that you could compare it with the
current 4.XX Manual, because the 3.22 manual is sort of 'out
of print' -- well you could find it on the web but it might
take you a  considerable amount of time. I didn't mean to
say that you could actually do it with 3.XX MySQL
statements. 

As to what you wrote earlier:
"Of course, I could do a php solution, but that seems less
elegant.
conceptually:
select id,field from table2
foreach(id)
select field from table1 where table1.id = id
update table2 set table2.field = field where table2.id = id"

I think what you should try is more like this:

A) Lock the table you want to update.

B) Select the records you want to update into a temporary
table.

C) Use a programatic (such as php) method to do the update
you want.

D) Delete the records in question from the locked table

E) Insert the temp records into the table.

The reason I would do it this way is that if you have a lot
of select activity on the table you could skip the locking
of the table in step A. and use a synchronization method
(like timestamps) to coordinate the deletion and insertion
steps.

As you probably are aware, the locking scheme in 3.2X is
table based. All selects will wait on pending updates (as
they should). But the effect of this is to make the database
seem real slow to some users when there are bursts of
activity.

Sorry -- I should have been more clear.

T. Gales & Associates
Helping People Connect with Technology
http://www.tgaconnect.com