mysql - 使用 mySQL 中的其他表更新值

Question

我对 MySQL 很陌生，正在做一个学校项目。我有这张表:

CREATE TABLE IF NOT EXISTS `Reserva` (
  `idReservacion` int(10) NOT NULL AUTO_INCREMENT,
  `importe` int(10) DEFAULT NULL,
  `fecha` date DEFAULT NULL,
  `idCliente` int(11) DEFAULT NULL,
  PRIMARY KEY (`idReservacion`),
  KEY `idCliente` (`idCliente`),
  CONSTRAINT `Reserva_ibfk_1` FOREIGN KEY (`idCliente`) REFERENCES `Cliente` (`idcliente`)
) ENGINE=InnoDB AUTO_INCREMENT=60001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我正在尝试通过使用下一个表格的更新来使导入成为生成的字段

CREATE TABLE IF NOT EXISTS `PaseDeAbordar` (
  `idPaseDeAbordar` int(11) NOT NULL AUTO_INCREMENT,
  `hora` time DEFAULT NULL,
  `idReservacion` int(5) NOT NULL,
  `idAsiento` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `idVuelo` int(11) NOT NULL,
  `precioc` int(10) DEFAULT NULL,
  PRIMARY KEY (`idPaseDeAbordar`),
  KEY `idReservacion` (`idReservacion`),
  KEY `idAsiento` (`idAsiento`),
  KEY `idVuelo` (`idVuelo`),
  CONSTRAINT `PaseDeAbordar_ibfk_1` FOREIGN KEY (`idVuelo`) REFERENCES `Vuelo` (`idvuelo`),
  CONSTRAINT `PaseDeAbordar_ibfk_2` FOREIGN KEY (`idReservacion`) REFERENCES `Reservacion` (`idreservacion`),
  CONSTRAINT `PaseDeAbordar_ibfk_3` FOREIGN KEY (`idAsiento`) REFERENCES `Asiento` (`idasiento`)
) ENGINE=InnoDB AUTO_INCREMENT=225001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Pasedeabordar 具有来自 Vuelo 和 Asiento 的外键，这是它们的结构:

CREATE TABLE IF NOT EXISTS `Asiento` (
  `idAsiento` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `idClase` int(11) NOT NULL,
  `precio` int(11) NOT NULL,
  PRIMARY KEY (`idAsiento`),
  KEY `idClase` (`idClase`),
  CONSTRAINT `Asiento_ibfk_1` FOREIGN KEY (`idClase`) REFERENCES `Clase` (`idclase`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Vuelo:

CREATE TABLE IF NOT EXISTS `Vuelo` (
  `idVuelo` int(8) NOT NULL AUTO_INCREMENT,
  `matriculaAvion` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `origen` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `destino` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `fechaVuelo` date NOT NULL,
  `horaSalida` time NOT NULL,
  `horaLlegada` time NOT NULL,
  `duracion` time NOT NULL,
  `tieneEscala` varchar(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'NO',
  `precio` int(10) NOT NULL,
  PRIMARY KEY (`idVuelo`),
  KEY `origen` (`origen`),
  KEY `destino` (`destino`),
  KEY `matriculaAvion` (`matriculaAvion`),
  CONSTRAINT `Vuelo_ibfk_1` FOREIGN KEY (`origen`) REFERENCES `Terminal` (`idterminal`),
  CONSTRAINT `Vuelo_ibfk_2` FOREIGN KEY (`destino`) REFERENCES `Terminal` (`idterminal`),
  CONSTRAINT `Vuelo_ibfk_3` FOREIGN KEY (`matriculaAvion`) REFERENCES `Avion` (`matricula`)
) ENGINE=InnoDB AUTO_INCREMENT=5501 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Importe 应该以这种方式生成:

reservacion.importe=(precio from asiento+precio from vuelo) where paseabordar.reservacionid=importe.reservacionid


UPDATE reservacion r, pasedeabordar pa, vuelo v, asiento a
SET r.importe=SUM(v.precio+a.precio)
WHERE pa.idreservacion=r.idreservacion
AND pa.idAsiento=a.idAsiento
AND pa.idVuelo=v.idVuelo;

但这会产生一个错误

Error(1111): Invalid use of group function

关于我做错了什么有什么想法吗？

Answer 1

SUM() 是一个分组函数，用于将同一列中的单元格相加。您只是尝试将同一行中的两个值相加。

r.importe=v.precio+a.precio 应该可以。

顺便说一下，显式连接表被认为是一种很好的做法，如下所示:

UPDATE reservacion r
JOIN pasedeabordar pa ON pa.idreservacion = r.idreservacion
JOIN vuelo v ON pa.idVuelo = v.idVuelo
JOIN asiento a ON pa.idAsiento = a.idAsiento
SET r.importe = v.precio + a.precio;