MySQL使用select和if else更新列

Question

我想要更新一个列，当从另一个表中选择不为空时，使用从另一个表中选择的日期，当它为空时使用恒定日期。

我的表：

CREATE TABLE Messe_Tag (
ID integer NOT NULL AUTO_INCREMENT,
Datum date NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE Messdiener (
ID integer NOT NULL AUTO_INCREMENT,
Name varchar(50) NOT NULL,
Letztes_dienen date DEFAULT '2017-01-01',
PRIMARY KEY (ID),
);

CREATE TABLE Messe (
ID integer NOT NULL AUTO_INCREMENT,
Datum integer NOT NULL,
PRIMARY KEY(ID),
FOREIGN KEY (Datum) REFERENCES Messe_Tag(ID)
);

CREATE TABLE Dienst_Messe (
ID integer NOT NULL AUTO_INCREMENT,
Messdiener integer,
Messe integer NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (Messdiener) REFERENCES Messdiener(ID),
FOREIGN KEY (Messe) REFERENCES Messe(ID)
);

我想从Messdiener中选择Messe中的最后一个日期。Messdiener和Messe通过Dienst_Messe关联，而来自Messe的日期与Messe_Tag关联。当select返回一个空结果时，我想用常量日期'2017-01-01'更新Messdiener.Letztes_dienen。当select返回一个日期时，我希望用该日期进行更新。

表的示例数据：

INSERT INTO Messe_Tag(ID,Datum)VALUES(1,'2017-07-11');
INSERT INTO Messe_Tag(ID,Datum)VALUES(2,'2017-05-23');

INSERT INTO Messdiener(ID,Name)VALUES(1,'Basti');
INSERT INTO Messdiener(ID,Name)VALUES(2,'Alex');

INSERT INTO Messe(ID,Datum)VALUES(1,1);
INSERT INTO Messe(ID,Datum)VALUES(2,2);

INSERT INTO Dienst_Messe(ID,Messdiener,Messe)VALUES(1,1,1);
INSERT INTO Dienst_Messe(ID,Messdiener,Messe)VALUES(1,1,2);

我尝试使用以下update语句执行此操作，但并未执行我想要的操作：

UPDATE Messdiener MES
LEFT JOIN Dienst_Messe DM ON DM.Messdiener = MES.ID
LEFT JOIN Messe M ON M.ID = DM.Messe
LEFT JOIN Messe_Tag MT ON MT.ID = M.Datum
SET MES.Letztes_dienen = CASE
WHEN MT.Datum IS NOT NULL
THEN MT.Datum
ELSE '2017-01-01' END
WHERE MES.ID = ?;

对于MES.ID = 1，我想用2017-07-11更新Messdiener.Letztes_dienen，因为这是最后一个日期。对于MES.ID = 2，我想使用2017-01-01进行更新，因为使用ID = 2的Messdiener在Dienst_Messe -> Messe -> Messe_Tag中没有日期。

我的update语句使用最旧的日期(2017-05-23)而不是最新的日期(2017-07-11)来更新列。如果MT.Datum为空，则该语句成功地将列Messdiener.Letztes_dienen更新为2017-01-01。

我认为我的update语句中需要一个ORDER BY来解决最早日期的问题，但我不知道如何实现它。

谢谢你的帮助。

Answer 1

UPDATE `Messdiener` SET `letztes_dienen` = (
    SELECT IFNULL(MAX(MT.Datum), '2017-01-01') FROM 
                  `Dienst_Messe` DM 
        LEFT JOIN `Messe`     M  ON M.ID  = DM.Messe
        LEFT JOIN `Messe_Tag` MT ON MT.ID = M.Datum 
    WHERE DM.Messdiener = Messdiener.ID
)
WHERE `id` = ?;

Answer 2

我已经尝试修改您的代码，因为最大日期可以在另外三个表中。

UPDATE Messdiener SET Letztes_dienen = (
SELECT IFNULL(
(SELECT MAX(D.Datum) FROM (
SELECT MT.Datum Datum FROM Dienst_Messe DM
LEFT JOIN Messe M ON M.ID = DM.Messe
LEFT JOIN Messe_Tag MT ON MT.ID = M.Datum
LEFT JOIN Messdiener ON DM.Messdiener = Messdiener.ID
WHERE Messdiener.ID = ?
UNION
SELECT MT.Datum Datum FROM Weihrauch_Messe WM
LEFT JOIN Messe M ON M.ID = WM.Messe
LEFT JOIN Messe_Tag MT ON MT.ID = M.Datum
LEFT JOIN Messdiener ON WM.Messdiener = Messdiener.ID
WHERE Messdiener.ID = ?
UNION
SELECT MT.Datum Datum FROM Kreuz_Messe KM
LEFT JOIN Messe M ON M.ID = KM.Messe
LEFT JOIN Messe_Tag MT ON MT.ID = M.Datum
LEFT JOIN Messdiener ON KM.Messdiener = Messdiener.ID
WHERE Messdiener.ID = ?
UNION
SELECT MT.Datum Datum FROM Assistent_Messe AM
LEFT JOIN Messe M ON M.ID = AM.Messe
LEFT JOIN Messe_Tag MT ON MT.ID = M.Datum
LEFT JOIN Messdiener ON AM.Messdiener = Messdiener.ID
WHERE Messdiener.ID = ?
) AS D)
, '2017-01-01'
)
WHERE ID = ?;

SELECT IFNULL(....) AS D), '2017-01-01');返回正确的日期，但在update子句中，它触发了一个错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 27

第27行是WHERE ID = ?;的最后一行为什么？你能帮帮我吗?

我的另一个尝试是：

UPDATE Messdiener SET Letztes_dienen = (
SELECT IFNULL(MAX(MT.Datum), '2017-01-01')
FROM Messe_Tag MT
LEFT JOIN Messe M ON M.Datum = MT.ID
LEFT JOIN Dienst_Messe DM ON DM.Messe = M.ID
LEFT JOIN Weihrauch_Messe WM ON WM.Messe = M.ID
LEFT JOIN Kreuz_Messe KM ON KM.Messe = M.ID
LEFT JOIN Assistent_Messe AM ON AM.Messe = M.ID
WHERE DM.Messdiener = Messdiener.ID
AND WM.Messdiener = Messdiener.ID
AND KM.Messdiener = Messdiener.ID
AND AM.Messdiener = Messdiener.ID
)
WHERE ID = 46;

但这会将Letztes_dienen直接设置为'2017-01-01'

Answer 3

我将使用view (无重复)，它包含所有包含日期的表。

-- I assumed that the structure of the tables is the identical.
CREATE VIEW all_data_view AS
    (SELECT id, datum from Kreuz_Messe) UNION (SELECT id, datum FROM Messe); 

检查结果的查询：

SELECT IFNULL(MAX(MT.Datum), '2017-01-01') FROM 
              `Dienst_Messe` DM 
    LEFT JOIN `all_data_view` AD  ON AD.ID  = DM.Messe
    LEFT JOIN `Messe_Tag` MT ON MT.ID = AD.Datum 
WHERE DM . Messdiener = 1;

因此，通过修改第一个答案中的DML (将表Messe替换为视图all_data_view)，您可以实现您的目标。