Can't specify target table t4 for update in FROM clause

Question

I am trying to update my table:

UPDATE offers t1
SET t1.deleted_at = NOW()
WHERE t1.id 
    NOT IN
    (
        SELECT f.id
        FROM (
        SELECT ean, MIN(net_price) as minprice
        FROM offers group BY ean
        ) 
        as x inner join offers as f on f.ean = x.ean and f.net_price = x.minprice
    );

can anybody help me with this issue? I realized that I needed to solve the issue through left join, but did not understand how to apply it in my case. Thank you.

Here is create table:

CREATE TABLE `offers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `net_price` decimal(10,2) unsigned DEFAULT NULL,
  `retail_price` decimal(10,2) unsigned DEFAULT NULL,
  `supplier_id` bigint unsigned DEFAULT NULL,
  `manufacturer_id` bigint unsigned DEFAULT NULL,
  `stock` int DEFAULT NULL,
  `ean` varchar(14) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mnn_id` bigint unsigned DEFAULT NULL,
  `source_id` bigint unsigned NOT NULL,
  `nds` smallint unsigned DEFAULT NULL,
  `to_export` tinyint(1) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `offers_supplier_id_foreign` (`supplier_id`),
  KEY `offers_manufacturer_id_foreign` (`manufacturer_id`),
  KEY `offers_mnn_id_foreign` (`mnn_id`),
  KEY `offers_source_id_foreign` (`source_id`),
  CONSTRAINT `offers_manufacturer_id_foreign` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `offers_mnn_id_foreign` FOREIGN KEY (`mnn_id`) REFERENCES `mnns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `offers_source_id_foreign` FOREIGN KEY (`source_id`) REFERENCES `sources` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `offers_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25762 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

user_2270762 · Answer

The error you are getting is

You can't specify target table 't1' for update in FROM clause

This is a problem specific to MySQL. You cannot directly access the updated table in a subquery, you need a sub subquery :-)

Replace

inner join offers as f

by

inner join (select * from offers) as f

hence to get the query working.

You don't need the join by the way. You can just write

UPDATE offers
SET deleted_at = NOW()
WHERE (ean, net_price) NOT IN
(
  SELECT ean, MIN(net_price)
  FROM (select * from offers) o
  GROUP BY ean
);

Another way to write this (update all rows for which exists a lower price for the same EAN):

UPDATE offers t1
SET t1.deleted_at = NOW()
WHERE EXISTS
(
  SELECT NULL
  FROM (SELECT * FROM offers) t2
  WHERE t2.ean = t1.ean
  AND t2.net_price < t1.net_price
);

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bfa6d74a72d40fa445ea5fa9f95803d1

By the way: You may want to add the condition deleted_at IS NULL to your update statement in order not to update rows that are already logically deleted, if such rows exists.

Can't specify target table t4 for update in FROM clause

1 Answers