Topic awaiting preservation: MySQL query help...

I've got two tables, lets call em 'table1' & table2' and each of them say have two columns

table1 has 'id' and 'data'

table2 has 'theid' and 'homeid'

Now I need to change the 'homeid' of a set of entires in table2 based on 'data' of table1.

Also 'id' of table1 is the same as 'theid' of table2

SELECT 1.id, 1.data, 2.homeid, 2.theid

				    FROM table1 1

		                    LEFT JOIN table2 2 ON (1.id=2.theid)

				    WHERE 1.data LIKE '%entry%' ORDER BY 1.id

Now that seems to work for the selecting the data, but when I try and do my updating, I think it's only grabbing the first result and updating instead of updating them all

$id = mysql_fetch_array ($result))

Update table2 SET homeid='new' WHERE homeid='old' AND theid="$id"

If your still with me i'd appreciate some help there

Synthetic's Chess Player Page

[This message has been edited by Synthetic (edited 07-22-2003).]

It looks like you are using Perl to do your updating of the database. Is that so?

Assuming that is the case, I'm guessing that mysql_fetch_array returns a reference to an array and you need to de-reference the array and loop through the list of returned IDs to update each of the records refered to. Something like:

foreach $id (@{$ids})

      { Update table2 SET homeid='new' WHERE homeid='old' AND theid="$id" }

Am I just going off in the wrong direction here/




-- not necessarily stoned... just beautiful.