0
UPDATE product 
SET product_layout='068'
WHERE (sku IN (SELECT DISTINCT master_sku FROM product)) 
    AND (master_sku IS NOT NULL AND master_sku !='') 
    AND sku LIKE 'IP-283-%';

It's giving me this error:

Error Code: 1093. You can't specify target table 'product' for update in FROM clause

This select statement is giving me what I need to update to product_layout ='069'

SELECT id, sku, master_sku 
FROM product 
WHERE (sku NOT IN (SELECT DISTINCT master_sku FROM product)) 
    AND (master_sku IS NULL OR master_sku = '' AND sku LIKE 'IP-%');

SO I go into Excel and create a concat update product set product_layout='069' where sku in (the concat list above);

Can anybody help out?

Thank you!

granch
  • 225
  • 3
  • 12
Boris
  • 149
  • 1
  • 8
  • https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Allen King Jul 20 '17 at 00:56

1 Answers1

1
UPDATE product AS p,
(
    SELECT p.sku
    FROM product AS p
    WHERE (sku not in (select distinct master_sku from product) and (master_sku is null or master_sku ='')) and sku like 'IP-%') AS newp
    set product_layout='069'
    WHERE p.sku=newp.sku
Boris
  • 149
  • 1
  • 8
  • granted this is for another issue of mine... where i was only able to use this, but didn't know how to make an update statement. --- select sku from product where (sku not in (select distinct master_sku from product) and (master_sku is null or master_sku ='')) and sku like 'IP-%'; – Boris Jul 20 '17 at 18:50
  • Thank you for this code snippet, which may provide some immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its educational value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with similar, but not identical, questions. Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Jul 21 '17 at 09:32