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

Question

I'm trying to write a query to update a schedule table's foreign key invoice_id.

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_date` date NOT NULL,
  `company_id` int(11) NOT NULL,
PRIMARY KEY (`id`))

CREATE TABLE `schedule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payment_date` date NOT NULL,
  `company_id` int(11) NOT NULL,
  `invoice_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_company_id_idx` (`company_id`),
  KEY `fk_schedule_invoices_idx` (`invoices_id`),
  CONSTRAINT `fk_schedule_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
  CONSTRAINT `fk_schedule_invoices` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

UPDATE schedule
SET invoice_id=(SELECT id FROM invoices ORDER BY id DESC LIMIT 1)
WHERE (company_id=1 
AND (SELECT EXTRACT(MONTH FROM payment_date) as schedule_month FROM schedule WHERE company_id=1)=
(SELECT EXTRACT(MONTH FROM invoice_date) as invoice_month FROM invoices WHERE company_id=1))

But I'm getting Error Code: 1093. You can't specify target table 'schedule' for update in FROM clause. Is there other way I can update invoice_id based on month and company_id?

EDIT: Actually what I want to achieve is to create a trigger which updates schedule table's invoice_id field after new invoice is inserted. That update should be based on company_id, year and month (days are different in the same month). I just tried to do an update without creating a trigger

Here's a sample

Can you please post a sample rowset of these two tables, and a sample of what the `schedule` table should look like after the `UPDATE`? It is difficult to say for sure, but your query seems like it is possible using an inner join whose `ON` condition is the `EXTRACT()` subselects. — Michael Berkowski, May 30 '16 at 20:06
I am unable to get the hang of your question: There is no such column `invoice_id` in `schedule`, but you are trying to `UPDATE` it? Could you (furthermore) explain, why a view on top of `schedule` isn't suitable for your case? — EagleRainbow, May 30 '16 at 20:45
Possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — rev_dihazum, May 31 '16 at 07:14
Sorry, I translated scheme to English and cut unnecessary fields, that's why I forgot to put invoice_id and company_id. I wrote an edit and uploaded sample table. Hopefully it will be cleared now. — Gabrielius B., May 31 '16 at 07:16

Gabrielius B. · Accepted Answer · 2016-05-31T12:27:00.860

0

As I mentioned in my post edit I needed to set schedule.invoice_id value. I'm very new to SQL, so I wrote a wrong condition at first. Thanks for the hint @Michael Berkowski

UPDATE schedule s
JOIN invoices i
ON s.company_id=i.company_id
SET s.invoice_id=i.id
WHERE month(s.payment_date)=month(i.invoice_date)

edited May 31 '16 at 12:27

answered May 31 '16 at 11:42

Gabrielius B.

71
2
4
12

Although this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight May 31 '16 at 12:04

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

1 Answers1