Mysql - 如果同一表的其他行中不存在值，则更新行

Question

我需要编写单个查询，如果该值已存在于同一个表中，它将更新行并跳过行。即，只有当该团队没有参加同一事件(eventID)的比赛时，我才想将球队更新为比赛。

这里是查询

update tbl_games m INNER JOIN bcs_event e on e.id = m.eventID SET m.team_id=1;

我尝试在 where 语句中遵循两种解决方案

WHERE(NOT EXISTS(select m1.id from tbl_games  as m1 where m1.eventID=e.id and m1.team_id=1 ))

This saying "You can't specify target table 'm' for update in FROM clause"

WHERE (NOT EXISTS(select * from (select m1.id from tbl_games as m1 where m1.eventID=e.id and m1.team_id=1) as tmp))

This is saying "Unknown column 'e.id' in 'where clause'"

这是样本数据

CREATE TABLE bcs_event (
  `id` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE tbl_games (
  `id` INT NOT NULL AUTO_INCREMENT,
  `event_id` INT NULL,
  `team_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `evid_idx` (`event_id` ASC),
  CONSTRAINT `evid`
    FOREIGN KEY (`event_id`)
    REFERENCES bcs_event (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

INSERT INTO bcs_event (`Name`) VALUES ('Softball Event');
INSERT INTO bcs_event (`Name`) VALUES ('Baseball Event');
INSERT INTO bcs_event (`Name`) VALUES ('Soccer Event');

INSERT INTO tbl_games (`event_id`, `team_id`) VALUES ('1', '11');
INSERT INTO tbl_games (`event_id`, `team_id`) VALUES ('1', '22');
INSERT INTO tbl_games (`event_id`) VALUES ('1');
INSERT INTO tbl_games (`event_id`) VALUES ('1'); 

update tbl_games m INNER JOIN bcs_event e on e.id = m.event_id SET m.team_id=11
where m.team_id is NULL and (NOT EXISTS(select g.id from tbl_games as g where g.eventID=e.id and g.team_id=11));

Answer 1

试试这个:

CREATE TABLE bcs_event (
  `id` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

✓

CREATE TABLE tbl_games (
  `id` INT NOT NULL AUTO_INCREMENT,
  `event_id` INT NULL,
  `team_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `evid_idx` (`event_id` ASC),
  CONSTRAINT `evid`
    FOREIGN KEY (`event_id`)
    REFERENCES bcs_event (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

✓

INSERT INTO bcs_event (`Name`) VALUES ('Softball Event');
INSERT INTO bcs_event (`Name`) VALUES ('Baseball Event');
INSERT INTO bcs_event (`Name`) VALUES ('Soccer Event');
INSERT INTO tbl_games (`event_id`, `team_id`) VALUES ('1', '11');
INSERT INTO tbl_games (`event_id`, `team_id`) VALUES ('1', '22');
INSERT INTO tbl_games (`event_id`) VALUES ('1');
INSERT INTO tbl_games (`event_id`) VALUES ('1');

✓

update tbl_games m 
INNER JOIN bcs_event e 
  on e.id = m.event_id 
SET m.team_id=11
where m.team_id is NULL and 
NOT EXISTS ( 
  select l.id 
  from (select g.id from tbl_games as g 
  where g.event_id=(SELECT e.id 
                         FROM   bcs_event e 
                         WHERE e.id = g.event_id) and g.team_id=11) l
  );

✓

db<> fiddle here