mysql - MySQL更新查询与内部表联接

我在mysql中有更新查询问题。
当我尝试这个

update planned_expense p1
set deleted=1
where case_id=204
  and deleted =0
  and type='MONTHLY'
  and planned_date>='2017-04-01'
  and id > (select min(id) from planned_expense p2 where
             p2.case_id = p1.case_id
               and  p2.planned_date = p1.planned_date
               and  p2.account = p1.account
               and p2.type = p1.type and p2.deleted = 0)

我懂了

您不能在FROM子句中指定目标表“ p1”进行更新

当我尝试

update planned_expense p1
set deleted=1
where case_id=204
and deleted =0
and type='MONTHLY'
and planned_date>='2017-04-01'
and id > (select min(id)
    from (select * from planned_expense p2
    where p2.case_id=p1.case_id and
    p2.planned_date=p1.planned_date
    and p2.account=p1.account and p2.type=p1.type and p2.deleted=0) p3)

我懂了

“ where子句”中的未知列“ p1.case_id”

我应该写些什么来更新那些记录？
谢谢你

这是我的桌子

DROP TABLE IF EXISTS `bes-ers`.`planned_expense`;
CREATE TABLE  `bes-ers`.`planned_expense` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `case_id` bigint(20) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `planned_date` datetime DEFAULT NULL,
  `addition_mark` int(11) DEFAULT NULL,
  `code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `planned_amount` decimal(22,4) DEFAULT NULL,
  `account` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `search_field` varchar(4096) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` varchar(4096) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by_id` bigint(20) DEFAULT NULL,
  `locked` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `FK_planned_expenses_1` (`case_id`),
  KEY `FK_planned_expenses_created_by_id` (`created_by_id`),
  KEY `planned_expense_planned_date` (`planned_date`),
  KEY `planned_expense_type` (`type`),
  KEY `planned_expense_deleted` (`deleted`),
  CONSTRAINT `FK_planned_expenses_1` FOREIGN KEY (`case_id`) REFERENCES `bankruptcy_case` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `FK_planned_expenses_created_by_id` FOREIGN KEY (`created_by_id`) REFERENCES `user` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=13172954 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

最佳答案

如果select中涉及到MySQL，则不允许更新表，但是您可以使用dinamica select表来避免此问题，例如：

    update planned_expense
      set deleted=1
      where case_id=204
      and deleted =0
      and type='MONTHLY'
      and planned_date>='2017-04-01'
      and id > ( select t.min_id from (select min(id)  min_id
                from planned_expense p2
                INNER JOIN planned_expense p1 on p2.case_id=p1.case_id
                        and  p2.planned_date=p1.planned_date
                    and p2.account=p1.account
                    and p2.type=p1.type and p2.deleted=0) t )

尝试添加一个复合索引为

create index idx_test on planned_expense (case_id, planned_date, account)

关于mysql - MySQL更新查询与内部表联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44847220/