help on an update query 1

[mekon]

hi all

I have two unrelated tables with identical fields, called products_now and products_next
the products are identified by the field sku_code

for all the products that are in BOTH tables ONLY, I need to update several fields in the products_now table with the equivalent data in products_next. I've tried the code below, as well as INNER JOIN variations, with no luck

UPDATE products_now SET products_now.product_name =
(SELECT products_next.product_name
FROM products_next
WHERE products_now.sku_code = products_next.sku_code);

any help would be greatly appreciated

thanks

 

[papparuff]

Try this;

UPDATE products_now INNER JOIN products_next ON products_now.product_name = products_next.product_name SET products_next.sku_code = [products_now].[sku_code]

John Ruff - The Eternal Optimist

 

[mekon]

thanks

is there any way to specify more than one field to be updated, or would a seperate query have to be made for each field update?

cheers

m

 

[papparuff]

You can update as many fields as you want. Just place a comman between the fields to update.

Here's a sample:
UPDATE products_now INNER JOIN products_next ON products_now.product_name = products_next.product_name, products_now.Cost = products_next.Cost, products_now.InvDate = products_next.InvDate SET products_next.sku_code = [products_now].[sku_code]
John Ruff - The Eternal Optimist

 

[mekon]

excellent. many thanks
one final question: is there any way to specify all the fields to be updated except, for example, a field called name, or do all the fields have to be manually inputted into the query?

thanks again

 

[papparuff]

You must place all the fields you want to update into the QBE John Ruff - The Eternal Optimist