mysql - 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');

标签： mysql

Answer 1

您可以尝试加入更新：

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
            );