Closed Thread Icon

Topic awaiting preservation: MySQL query help... (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=12828" title="Pages that link to Topic awaiting preservation: MySQL query help... (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: MySQL query help... <span class="small">(Page 1 of 1)</span>\

 
Synthetic
Paranoid (IV) Inmate

From: under your rug,
Insane since: Jul 2001

posted posted 07-22-2003 21:13

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

code:
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

code:
$id = mysql_fetch_array ($result))



code:
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).]

hyperbole
Paranoid (IV) Inmate

From: Madison, Indiana, USA
Insane since: Aug 2000

posted posted 07-23-2003 22:11

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:

code:
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.

« BackwardsOnwards »

Show Forum Drop Down Menu