Mysql 更新查询与内表连接

Question

我在 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”

我应该写什么才能更新这些记录？打败你。

这是我的 table

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;

Answer 1

如果涉及 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)