I use this query to check that data in my products table is current on my daily csv file.

select t2.PRODCODE, t2.products_id, t2.products_quantity
from products as t2
left outer
join CSV as t1
on t1.PRODCODE = t2.PRODCODE
where t1.PRODCODE is null

This brings a list up. I find this very useful but I need to know how to convert this type of query to an update. Basically I want to be able to update products.products_status to 0 if it appears on this list or alternatively delete the rows appearing on this list. I have tried repalcing the the word select with update or delete but it doesnt seemt o work.

Any pointers would be helpful

Thanks

you could modify that query and use is as a sub select as your feeder to either update or delete.

I think you need to think clearly about what the requireed outcomes are. I dont think you can update joined tables (I'm pretty sure only access/JET can do that) in MySQL.

it slook sto me as if you may have 2 main queries suignt he same subselect

Hi

Thanks for getting back to me. I forgot to point out that I am very new to this. I only understand that very basics and even the query listed above is way beyone me.

All I know is that at the moment running this query blings up a list of about 300 items that are no loner stocked that I either need to remove or change to a status of "0". At the moment I have to print off the list and manually edit each one using

update products
set products_status = 0
where products_id = "input ID here"

As you can imagine this is very time consuming and I am sure there must be an easier way,.

Thanks

sorry, but mysql's multiple-table update capabilities are far more comprehensive than those of access

Wow, do you live in every mySQL forum? lol

yes, and forums for other database systems too

So is it possible to use this kind of join with an update? and is it possible to use aliases for the table in this way with an update?

To get you back on track again, the sub select that Healdem was referring to would be something like this.
My appologies for not be able to test this code before posting it, so do not try this against a production database please. Eventhough I did not test the code, but I'm pretty sure this would do the job:

HTH ...

Gr,
Yveau

Hi this helps a lot. thanks


So just to get my head around this a subselect is where you have a query as normal but looking at the data slreadyselected from another query. Is that correct?

Thanks

Yep, hence SUBselect

Gr,
Yveau

I have today tried this sub select query out.

I this error

#1093 - You can't specify target table 'products' for update in FROM clause

I am guessing this has something to do with the fact that I am using an alias in the sub select part? I have tried moving a couple of things around but with no luck.

please refresh our memories -- which query did you run?

also, do you understand the error message you were given?

I think ollyno1uk means my code ...

From the MySQL 5.0 manual:
... Hmm, thought you couldn't mention the updated columns in the subquery, turns out to be the complete table
My mistake ... Anyway, you could solve this by making a temporary copy of the products table and refer to that one in the subquery.

Let's say you created the temporpary products table and called it TempProd, the complete query would end up looking like this:
... and don't forget to get rid of the TempProd table after this update succeeded.
Sorry for the incorrect code the first time ...

Gr,
Yveau

Hi

Yes it was the query that Yveau01 gave me. I think i understood the error in so much that It was saying i could not reuse a table name that was in as an alias elswhere.

Yveau:

Thanks very much for the modified query. I will try to run this shortly.

Hi again

Yes this method worked fine.

Is there a better way of achieving this without the need for copying the entire table?

Thanks for all your help