mysql - 将值插入从父表引用的子表中

我昨晚整晚都在想办法解决这个问题，但运气不好，所以我希望你们能帮忙，因为我没什么主意：
我有两个父表，要从中填充连接表：
新娘：

create table if not exists `Brides` (
    `BrideID` INT not null auto_increment,
    `MaidenName` varchar(10) unique,
    primary key (`BrideID`)
   ) engine=InnoDB;

    insert into Brides (MaidenName)
    values ('Smith'),
           ('Jones')
    ;

教堂：

create table if not exists `Churches` (
`ChurchID` INT not null auto_increment,
`ChurchName` varchar(10) unique,
primary key (`ChurchID`)
) engine=InnoDB;

insert into Churches (ChurchName)
values ('St Marys'),
       ('St Albans')
;

我试图通过间接引用每个父表中的唯一名称来填充连接表合并的ID变量。此外，我还希望包括MarriedName以确定新娘是否结婚不止一次：
婚姻：

create table if not exists 'Marriages' (
'BrideID' INT not null,
'ChurchID' INT not null,
'MarriedName' TEXT not null
primary key ('BrideID','ChurchID','MarriedName')
  INDEX `fk_Marriages_Brides1_idx` (`BrideID` ASC),
  INDEX `fk_Marriages_Churches1_idx` (`ChurchID` ASC),
  CONSTRAINT `fk_Marriages_Brides1`
    FOREIGN KEY (`BrideID`)
    REFERENCES `Brides` (`BrideID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Marriages_Churches1`
    FOREIGN KEY (`Church_ID`)
    REFERENCES `Churches` (`ChurchID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
;

我正在尝试下面的伪代码（尽管我很确定这不是一种明智的方法，因为处理这么多子查询肯定会很慢）：

insert into Marriages (Bride_ID, Church_ID, MarriedName)
  select b.BrideID, c.ChurchID, m.MarriedName
  from (values (Bride,Church,MarriedName)
         ('Smith','St Marys','Johnson'),
         ('Jones','St Albans','Peterson')
         ) m

  join Brides b
  on a.MaidenName=m.Bride
  join Churches c
  on m.Church=c.ChurchName;

如果您有任何帮助/见解/更正，我们将不胜感激！

最佳答案

试试看：

INSERT INTO Marriages
SELECT b.BrideID, c.ChurchID, 'Johnson'
FROM Brides b, Churches c
WHERE b.MaidenName='Smith' AND c.ChurchName='St Marys'