MySQL "UPDATE Query" Help..PLEASE

[Ocean Sailor]

I really need help, not a db guy..willing to learn hard. Going on the 7th week on this..&%#%.

Problem:
Updating a specific Table record (Inventory) from another table based on a conditional equal record (primaryKey) that exits in both tables.
--> Primary key used to find the records for both tables
--> compared the 2 table records based on the same primary key
--> updated specific records that are differnt in one table

Update Query Syntax (Anonymous help..thanks for it):
UPDATE {table-name}
SET {column-name} = {expression} [, {column-name} = {expression} ...] [WHERE {condition}]

Proposed Solution (not working):

UPDATE Table1
SET Quantity = (
SELECT Table2.Quantity
FROM Table2
WHERE Table1.PrimaryKey = Table2.PrimaryKey
)
WHERE EXISTS (
SELECT *
FROM Table2
WHERE Table1.PrimaryKey = Table2.PrimaryKey
AND Table1.Quantity <> Table2.Quantity
)

Error Message from MySQL:
/* Error : "You have an error in your SQL syntax near 'SELECT rsr23.Inventory FROM
rsr23 WHERE products.pID = rsr23.rsr ) WHERE EXISTS ( S' at line 1" */

So basicly the first line of the proposed solution, seems not to work in MySQL;
SET Quantity = (
SELECT Table2.Quantity
FROM Table2
WHERE Table1.PrimaryKey = Table2.PrimaryKey
)

Ideas...Please:
Any help would be greatly rewarded....
thks...

[RocketJeff]

Subqueries don't work in the version of mySQL that Powweb's running (they're in version 4.1 - the next major relase). Powweb is running the most current release, so this isn't their fault.

When working with mySQL, you'll have to remember that while it's fast, it is not a complete relational database (although it's getting closer).

[Ocean Sailor]

Thanks... RocketJeff for your inside.....

Through your inside RocketJeff ...... "Don" who participated in the "Synametrics Forums" under www.synametrics.com couldent belief it.

Jet he posted a great solution that works with version above 4.0.4 MySQL... and it has the folowing Syntax for an conditional update based on one constant integrer (PrimaryKey):


UPDATE column1, table1
SET table1.column1 = table2.column2
WHERE table1.PrimaryKey = table2.PrimaryKey

thanks to all of you..... great job!