mysql - 如何从 MySQL 中的另一个表插入现有值

Question

我在这里想做的是将要检索的 CompositionCollection 表中的 PerformDate 值插入到 Compositions 表中的 PerformDate 中，而无需手动键入它。因此，每个乐曲将具有从乐曲集合中播放时的值。

成分表中的最后一个引号空间是日期。

CREATE TABLE Compositions(
CompositionId CHAR(6) NOT NULL,
Composer VARCHAR(25) NOT NULL,
Name VARCHAR(30) NOT NULL,
YearWritten VARCHAR(4) NOT NULL,
PerformerId CHAR(6) NULL,
PerformDate DATETIME NULL,
CONSTRAINT pk_compositions PRIMARY KEY (CompositionId)
);

CREATE TABLE CompositionCollection(
ConcertId CHAR(6) NOT NULL,
CompositionId INT NOT NULL,
PerformerId CHAR(6) NOT NULL,
PerformTime TIME NOT NULL,
PerformDate DATE NOT NULL,
CONSTRAINT pk_pTime PRIMARY KEY (PerformTime)
);


-- compositions data values
INSERT INTO Compositions VALUES 
('CC01', 'Alexander Borodin', 'Scherzo in a 
Flat', '1883', '', '2017-09-17'),
('CC02', 'Alexander Borodin', 'Symphony No 
2', '1869', '', ''),
('SC01', 'Beethoven', 'Symphony No 6', 
'1802', '', ''),
('SC02', 'Edward Elgar', 'Cello Concerto', 
'1997', '', '');

-- composition collection data values
-- All the values below is for one concert and they are all played at the 
-- same date
INSERT INTO CompositionCollection VALUES 
('123456', 'CC01', 'C012', 
'08:00:00', '2017-09-17'),
('123456', 'CC02', 'C012', 
'08:15:00', '2017-09-17'),
('123456', 'SC01', 'S512', 
'08:20:00', '2017-09-17'),
('123456', 'SC02', 'C012', 
'08:40:00', '2017-09-17');

Answer 1

您可以使用触发器:

CREATE TRIGGER last_played
AFTER INSERT
ON CompositionCollection FOR EACH ROW

BEGIN
 UPDATE Compositions SET PerformDate = New.PerformDate WHERE CompositionId = New.CompositionId;
END;

更多关于mySQL Triggers