title: Error Code: 1093. You can't specify target table 'schedule' for update in FROM clause
body: <p>I'm trying to write a query to update a <code>schedule</code> table's foreign key invoice_id.</p>
  
  <pre><code>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))
  </code></pre>
  
  <p>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 <code>invoice_id</code> based on month and company_id?</p>
  
  <p><strong>EDIT:</strong>
  Actually what I want to achieve is to create a trigger which updates <code>schedule</code> 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</p>
  
  <p><a href="http://i.stack.imgur.com/Sxqie.png" rel="nofollow">Here's a sample</a></p>