update table from another table based on results

[ryan king]

right now I have a table from the zencart application that holds artist_id, products and genre. I have another program Ive intergrated it with that has a column for genre. Ide like to update my other table with this genre info.

 

Only problem is this.. artist in zencart are not directly associated with genres, they are associated with producst and those products are associated with genres.

In my other table, (the one im tryin to update) artists are directly associated with genres.

 

Since all the artists products are typicly under the same genre as the artist themselves, I would like to copy this data over to the correct artist id in my other table so all my artist are DIRECTLY associated with a artist genre.

 

heres how I was atempting to do this..

 

$result = mysql_query("SELECT * FROM zoo_music_genre, zoo_product_music_extra, jamroom_band_info WHERE jamroom_band_info.band_id=zoo_product_music_extra.artists_id AND zoo_product_music_extra.music_genre_id=zoo_music_genre.music_genre_id ORDER BY zoo_music_genre.music_genre_name DESC");

while($row = mysql_fetch_array($result))

{
$genre = $row['music_genre_name']; 


    $sql_update = "UPDATE jamroom_band_info SET band_soundlike = $genre"; 
    mysql_query($sql_update) or die(mysql_error()); 

 

my code my not even be correct becuase im somewhat of a newbie, but if run the select query without the update stuff I get the proper results echoed.

whe I run the update command I get an error stating "Column does not match value at row 1"

 

I believe this is true becuase an artist in the zencart DB might have 10 products each, I just need to pick the first one, take the genre from it and insert it into my other genre table, then go to the next artist id and so on...so only one per record.. I think its failing becuase it might pull artist id 10 times becuase of the products being associated with the genres.. am I making any sense?

[fenway]

1) You have no WHERE clause with your UPDATE statement -- hope you had a backup.

 

2) You can do this with a single query.