hello, i've had to change some of my tables to accomodate some greater flexibility in the application that uses it and because of this i need to go through and update all the records. i've done one table by hand and it had about 100 records and took about 20 minutes. but this next table has about 550 records and i really don't feel like doing this all by hand. i'm using MySQL Control Center to do this editing so i'd like to know if there's a single SQL statement i could use to update all the rows.
here is a simple representation: products: (pay no attention to the poor choice in column names. this is a retrofitting and will be fixed in later versions.) +----------+---------------+ | id | sequential_id | +----------+---------------+ | PRDX-41 | 1 | | ABCX-01 | 2 | | FF00-11 | 3 | \/\/\/\/\/\/\/\/ | ETC0-99 | 500 | +----------+---------------+ the 'prod_sequential_id' column was added later to the products_categories table. products_categories: +-----+---------+--------------------+--------+ | id | prod_id | prod_sequential_id | cat_id | +-----+---------+--------------------+--------+ | 1 | PRDX-41 | 0 | 41 | | 2 | PRDX-41 | 0 | 15 | | 3 | ABCX-01 | 0 | 13 | | 4 | FF00-11 | 0 | 89 | \/\/\/\/\/\/\/\/ | 610 | ETC0-99 | 0 | 41 | +-----+---------+--------------------+--------+ so... as you can see, prod_sequential_id has all 0's in its column. it should contain the value of products.sequential_id WHERE products_categories.prod_id = products.id. the problem is that i'm not sure how to do this all in one statement (or if it's even possible): (i know the following does not work, but it's basically the logic i think i need.) UPDATE products_categories AS pc, products AS p SET pc.prod_sequential_id = p.id WHERE pc.prod_id = p.id; thanks for your help. chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
