PostgreSQL数据库,要求为输入的值(‘10’)小于数据库中的字段 outline_achieve_level 时,更新字段为10,并将 outline_end_day 设置为空。
因为IF,以及MAX都无法使用,所以目前只能采取这种方式,希望能找到更好的方法
UPDATE
tbl_system_project
SET
outline_achieve_level = ( CASE
WHEN (SELECT
outline_achieve_level
FROM
tbl_system_project
WHERE
project_no = '00270'
AND
project_phase = '006') >'10'
THEN
'10'
ELSE
(SELECT
outline_achieve_level
FROM
tbl_system_project
WHERE
project_no = '00270'
AND
project_phase = '006')
END ),
outline_end_day = CASE
WHEN (SELECT
outline_achieve_level
FROM
tbl_system_project
WHERE
project_no = '00270'
AND
project_phase = '006') >'10'
THEN
NULL
ELSE
(SELECT
outline_end_day
FROM
tbl_system_project
WHERE
project_no = '00270'
AND
project_phase = '006')
END