HHHHHEEEEELLLLLLPPPPPPPPPPP

[NGR]

Hi there people

 

Right, does anyone know how to run a query that will compare two tables and if they differ update the different field...

 

For example, i have two tables - one called rectron and one called test!

 

The test table needs to be updated if the price in the rectron table changes, now it should be strightforward but its not working - here's what im using!

 

--------------------------------------------------------------------

UPDATE test

SET test.products_price = rectron.products_price

WHERE test.products_model = rectron.products_model

--------------------------------------------------------------------

this is the error message:

MySQL said:

 

 

Unknown table 'rectron' in where clause

--------------------------------------------------------------------

Help, what am i doing wrong, tried :

 

UPDATE test

SET test.products_price = (SELECT products_price FROM rectron)

WHERE test.products_model = (SELECT products_model FROM rectron)

-----------------------------------------------------------------------

and still get error messages...it should be simple but it isnt....

 

Am i on the right track here now - to explain what im doing - my supplier sends me a pricelist every week with updated prices - the model number of the product stays the same, the price changes... so what im doing is importing the new prices and the model from an excel file into a rectron table and then comparing the differences with the "test" table...is that right? Or is there an easyier way to do it?

 

Any help will be appreciated so much,

Sweet,

Nick

[wizardsandwars]

Example number one isn't even valid SQL syntax on any DBMS. Example number two is usgin a subquery in an update statement, which MySQL deosnt' support, yet.

 

The solution is to utilize mySQL's new support for multi-table updates. You have to join the two tables together in the update.

[NGR]

Ok, looked on the mysql helpline...says something about going like this :

 

UPDATE test, rectron

SET test.products_price = (SELECT products_price FROM rectron)

WHERE test.products_model = (SELECT products_model FROM rectron)

 

Still not helping much...arrrrrrgggggggggggggggggg!!!!!!!!

[nickos]

Ok, looked on the mysql helpline...says something about going like this :

 

UPDATE test, rectron

SET test.products_price = (SELECT products_price FROM rectron)

WHERE test.products_model = (SELECT products_model FROM rectron)

 

Still not helping much...arrrrrrgggggggggggggggggg!!!!!!!!

Maybe you can try this:

 

UPDATE test, rectron

SET test.products_price = rectron.products_price

WHERE test.products_model = rectron.products_model

 

in msaccess this works but I'm not so sure for mysql database

 

second solution may be

 

1)

use of CREATE TABLE bar (UNIQUE (fields...)) SELECT (desired fields to compare for both tables) FROM test, rectron where test.keyid=rectront.keyid; // you create there one temporary table with two tables in it.

 

2)

update desired table with data from temporary table

 

 

Also I think there must be other solutions for your problem

 

nickos

[NGR]

:huh: Nope, that didnt wanna work either....Um, anybody else have any idea's?

 

Thanks anyways!!!

 

Nick ;) :P

[NGR]

:blink: Hey

 

Does nobody else have any idea's??? Please please pretty please if you know please tell me???

 

Nick :ph34r:

[NGR]

:D Yiiiiippppeeeeeeeeeeee

 

Yipppppepeeeeee..... it works it works - Nico, ur my hero... that MsAccess way worked....

 

Thanks ALOT bro B)