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