Re: sql statement help needed

----- Original Message ----- From: "Chris W. Parker" <cparker@swatgear.com> To: <php-general@lists.php.net> Sent: Thursday, July 01, 2004 3:33 AM Subject: [PHP] sql statement help needed > 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; why can't you do like this. UPDATE products_categories set prod_sequential_id = prod_id in one go. Thanks Binay > > > thanks for your help. > chris. > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >