If I understand you, you're wanting to do an update based on a join. I can
give you standard syntax to do this, but when I tried it on the older
version of MySQL that I have here, it was not supported. I don't stay very
current with MySQL because I don't use it, so the version you are using
might support this. Try it and see. If not, this is one situation where I
would seriously look at another DBMS rather than frequently go through a
procedure to update 20,000 rows individually - but that's just an opinion -
don't flame me for it. (I understand it might not even be possible for you
to switch)
The Standard SQL syntax for updating based on a join is this
UPDATE Table1
Set Column1 = r.Column1, Column2 = r.Column2
FROM Table1 l INNER JOIN Table2 r ON l.Column3 = r.Column3
That should be very quick. In that one, Column3 would be the keys that match
between Table1, and Table2. You don't use the alias on the columns being
updated because the table to be updated is specified first, but you must use
the alias of the columns when you are setting that column to a value from
the other table in the join.
Does that make sense? Hope it will work for you - you'll just have to try it
to see.
Steve H.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 10, 2001 4:59 PM
To: [EMAIL PROTECTED]
Subject: Bulk Updates Using Joins or Some Such Nonsense
Dear All,
Using MySQL I'm trying to update field1 in table1 with data from field1 in
table2 where the primary keys in tables 1 and 2 match. In other words I have
2 tables the first with direction information and the second with speed
information both have time as the primary key and what I want is a single
TEMPORARY table with the direction and speed indexed by time. Table 1 is a
copy of the speed information with an extra field to accommodate the
direction information.
So far I've tried
$query = qq{SELECT f_dir.t_table1, f_speed.t_table2 WHERE f_time.t_table1 =
f_time.t_table2};
while my $hasharray = ....
{
$query = qq{UPDATE...
}
which is very slow (the tables contain 20 000 records).
Another option would be to select the data into a text file, delete existing
data from the table1 and bulk load the text file into the table, which seems
extremely clumsy.
Is there an option I've missed?
Ta,
Scott
