Need an SQL query to select a range of products and update the product status to 0

[oztraveller06]

Hi all,

Long time lurker here and Zencart user since 2010 but first ever post, as I've always been able to find what I needed to know by reading the forums, and the responses from the very helpful people here

However this one has me stumped. My situation is:

Zencart 1.51 with around 2,000 products (books)

I haven't updated the DB for a while and after doing some housekeeping I found that 400 of these books are now out of print, hence I want to disable them from showing but keep them in the database - in other words, set product_status to 0.

I've had a play around in MySQL attempting to get this to work, but don't seem to be able to get the syntax right.

I've created a new table with all the out of print products with just the ISBN (equivalent to products_model in my DB) as the key, and done an inner join on the DB products table against the "out of print" products table.

That's all fine and I now have a select list of 400 matching products. For the life of my I cannot work out how to then update the DB products table to set these selected products to products_status=0...

I know there are tools like EZ Populate which I believe would do the job but I think this is a relatively simple update to do, once I know the correct syntax


Thanks all!

[oztraveller06]

Murphy's Law said that I would find the answer 10 minutes after I posted the question...

UPDATE products
INNER JOIN
books_out_of_print
ON products.products_model = books_out_of_print.isbn
SET products.products_status = 0

Query OK, 398 rows affected (1.45 sec)


Thanks all - hopefully this may help someone else in the same situation!

Cheers

[mc12345678]

Murphy's Law said that I would find the answer 10 minutes after I posted the question...

UPDATE products
INNER JOIN
books_out_of_print
ON products.products_model = books_out_of_print.isbn
SET products.products_status = 0

Query OK, 398 rows affected (1.45 sec)


Thanks all - hopefully this may help someone else in the same situation!

Cheers

Glad you figured it out, personally at the onset of reading the expectations (~400 products) the concern was how to uniquely identify the 400 products. Was going to suggest just what you did (select search the products then modify the search to an update). So I would say you had done the hardwork, just needed a refresher. :) happy zenning!!!