mysql更新条件:失败

Question

我目前正在尝试用MySQL创建一个简单的表，它满足以下条件。

如果该船是在“日本”建造的，则将结果数据更新为“已沉没”。

​

​

我试过几个代码，但都失败了。

下面是我一直在尝试的代码

update Outcomes
    set result = 'sunk' 
where ship = (
                select name 
                from Ships 
                    natural join Classes as A 
                where Classes.country = 'Japan' 
                and Outcomes.ship =  A.name
            );

我为你可能需要的情况添加了数据输入查询。

create table Classes(
    Class varchar(20), 
    type char(20), 
    country varchar(20), 
    numGuns int, 
    bore int, 
    displacement int,
    primary key(Class)
);

create table Ships(
    name varchar(20), 
    Class varchar(20), 
    launched int,
    primary key(name),
    foreign key(Class) references Classes (Class)
);

create table Outcomes(
    ship varchar(20), 
    battle varchar(20), 
    result varchar(10),
    foreign key(battle) references Battles(Name),
    foreign key(ship) references Ships(name)
);

insert into Classes values 
('Bismark', 'bb', 'Germany', 8, 15, 42000),
('Iowa', 'bb', 'USA', 9, 16, 46000),
('Kongo', 'bc', 'Japan', 8, 14, 32000),
('North Carolina', 'bb', 'USA', 9, 16, 37000),
('Renown', 'bc', 'Gt. Britain', 6, 15, 32000),
('Revenge', 'bb', 'Gt. Britain', 8,15, 29000),
('Tennessee', 'bb', 'USA', 12, 14, 32000),
('Yamato', 'bb', 'Japan', 9, 18, 65000);

insert into Ships values
('Prince of Wales', 'Tennessee', 1921),
('Bismark', 'Bismark', 1915),
('Duke of York', 'Kongo', 1914),
('Iowa', 'Iowa', 1943),
('Kirishima', 'Kongo', 1915),
('Kongo', 'Kongo', 1913),
('Fuso', 'Iowa', 1943),
('Yamashiro', 'Yamato', 1942),
('California', 'Iowa', 1943),
('North Carolina', 'North Carolina', 1941),
('Renown', 'Renown', 1916),
('Hood', 'Renown', 1916),
('Scharnhorst', 'Revenge', 1916),
('Revenge', 'Revenge', 1916),
('King George V', 'Revenge', 1916),
('South Dakota', 'Revenge', 1916),
('Tennessee', 'Tennessee', 1920),
('Washington', 'North Carolina', 1941),
('West Virginia', 'Iowa', 1943),
('Yamato', 'Yamato', 1941);

insert into Outcomes values 
('Bismark', 'North Atlantic', 'sunk'),
('California', 'Surigao Strait', 'ok'),
('Duke of York', 'North Cape', 'ok'),
('Duke of York', 'Surigao Strait', 'ok'),
('Fuso', 'Surigao Strait', 'sunk'),
('Hood', 'North Atlantic', 'sunk'),
('King George V', 'North Atlantic', 'ok'),
('Kirishima', 'Guadalcanal', 'sunk'),
('Prince of Wales', 'North Atlantic', 'damaged'),
('Prince of Wales', 'North Cape', 'ok'),
('Scharnhorst', 'North Cape', 'sunk'),
('South Dakota', 'Guadalcanal', 'damaged'),
('Tennessee', 'Surigao Strait', 'sunk'),
('Washington', 'Guadalcanal', 'ok'),
('West Virginia', 'Surigao Strait', 'ok'),
('Yamashiro', 'Surigao Strait', 'ok');

Answer 1

正确的UPDAtE必须是来自船的船的名称，而不是类的名称。

update Outcomes
        set result = 'sunk' 
    where ship = (
                    select name 
                    from Ships B
                        natural join Classes as A 
                    where A.country = 'Japan' 
                    and Outcomes.ship =  B.name
                );

请参阅示例https://dbfiddle.uk/?rdbms=mysql

Answer 2

您可以尝试联合更新：

update Outcomes
join   Ships
on     Outcomes.ship = Ships.name
join   Classes
on     Classes.class = Ships.class
set    Outcomes.result = 'sunk'
where  Classes.country = 'Japan'

我明白你的错误所在，那就是你加入了and Outcomes.ship = A.name而不是and Ships.class = A.name ( A是Classes的别名)。此外，您可以通过其实际名称(Classes)来引用Classes表，尽管您为它设置了别名(A)。据我所知，这是不允许的：

update Outcomes
    set result = 'sunk' 
where ship = (
                select name 
                from Ships 
                    natural join Classes as A 
                where Classes.country = 'Japan' <<< Here you refer to Classes instead of A
                and Outcomes.ship =  A.name <<< This doesn't make sense to me
            );