Have you tried this other way of making an inner join?
UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id =
p.id
SET pc.prod_sequential_id = p.id
But that does not seem right our you could say:
UPDATE products_categories AS pc
SET pc.prod_sequential_id = pc.prod_id
and have the same statement. I think this is what you meant to say:
UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id =
p.id
SET pc.prod_sequential_id = p.sequential_id
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
|---------+---------------------------->
| | "Chris W. Parker"|
| | <[EMAIL PROTECTED]|
| | .com> |
| | |
| | 07/06/2004 01:14 |
| | PM |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: <[EMAIL PROTECTED]>
|
| cc:
|
| Fax to:
|
| Subject: update query question
|
>--------------------------------------------------------------------------------------------------------------------------------|
hello,
i've had to change some of the tables in my db 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
