sql - Postgresql 在 UPDATE 请求中添加 WHERE 子句

Question

我打算写一个 SQL 请求 UPDATE，我想验证 table2.fld5 是否已经包含 'Hello' 为了不写几次，但没有找到实现此验证的方法。

请求是:

UPDATE table2
    SET fld5 = concat(fld5, 'Hello')
    WHERE NOT EXISTS (SELECT *
                      FROM table1
                      WHERE table1.fld1 = table2.fld1 AND
                            table1.fld2 = table2.fld2 AND
                            table1.fld3 = table2.fld3
                     )
     AND table2.fld4 > (NOW() - INTERVAL '79 MINUTE');

Answer 1

也许您的日期检查需要在现有子查询之外:

UPDATE table2 SET fld5 = concat(fld5, 'Hello') 
WHERE fld5 NOT LIKE '%Hello%' AND table2.fld4 > (NOW() - INTERVAL '79 MINUTE'
AND NOT EXISTS (SELECT * FROM table1 WHERE table1.fld1 = table2.fld1 AND table1.fld2 = table2.fld2 AND table1.fld3 = table2.fld3))

;