我有以下 upsert 問題，因為子查詢給了我多個結果。問題是我不知道如何在 upsert 中指示來比較當前正在更新的列的值。問題出在 DO UPDATE 之后。
INSERT INTO tbl_ws_gps_history(nm, date, "maxOdometer", "minOdometer", "kmDifferencePerDay", "currentOdometer")
SELECT nm, fecha,
MAX("odommetro"),
MIN("odommetro"),
(MAX("odommetro") - MIN("odommetro")),
MAX("odommetro")
FROM tbl_admon_gps_data
WHERE fecha = CURRENT_DATE
GROUP BY nm, fecha
ORDER BY nm
ON CONFLICT (nm, date) DO UPDATE
SET "maxOdometer" = (SELECT MAX(d."odommetro") FROM tbl_admon_gps_data d, tbl_ws_gps_history h WHERE d.fecha = CURRENT_DATE AND d.nm = h.nm AND d.fecha = h.date GROUP BY d.nm, d.fecha ORDER BY d.nm),
"kmDifferencePerDay" = (SELECT (MAX("odommetro") - MIN("odommetro")) FROM tbl_admon_gps_data WHERE fecha = CURRENT_DATE GROUP BY nm, fecha ORDER BY nm),
"currentOdometer" = (SELECT MAX("odommetro") FROM tbl_admon_gps_data WHERE fecha = CURRENT_DATE GROUP BY nm, fecha ORDER BY nm);
uj5u.com熱心網友回復：
我認為您正在尋找代表將要插入的行的excluded記錄：
INSERT INTO tbl_ws_gps_history(nm, date, "maxOdometer", "minOdometer", "kmDifferencePerDay", "currentOdometer")
SELECT nm, fecha,
MAX("odommetro"),
MIN("odommetro"),
(MAX("odommetro") - MIN("odommetro")),
MAX("odommetro")
FROM tbl_admon_gps_data
WHERE fecha = CURRENT_DATE
GROUP BY nm, fecha
ORDER BY nm
ON CONFLICT (nm, date) DO UPDATE
SET "maxOdometer" = excluded."maxOdometer",
"kmDifferencePerDay" = excluded."kmDifferencePerDay",
"currentOdometer" = excluded."currentOdometer";
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/526452.html
上一篇：在SQL中使用多個條件洗掉重復項