DB2 case语句，用于更新一个表但多个列

Question

我正试图将4个独立的更新语句合并为一个更新语句。我没有案例陈述的经验，也没有我试图做的事情是否可行的经验。

有关于如何构建的意见吗？这是我的尝试，但不起作用。

UPDATE URL
SET CONTENT_TITLE =
CASE
WHEN ID = ? AND SYSTEM_UPDATE_REASON = ? AND CONTENT_TITLE IS NOT NULL AND ? NOT BETWEEN 400 AND 599 OR ID = ? AND SYSTEM_UPDATE_REASON = ? AND CONTENT_TITLE != '' AND ? NOT BETWEEN 400 AND 599 THEN ?
WHEN ID = ? AND CONTENT_TITLE IS NULL AND ? NOT BETWEEN 400 AND 599 OR ID = ? AND CONTENT_TITLE = '' AND ? NOT BETWEEN 400 AND 599 THEN ?
WHEN ID = ? AND CONTENT_TITLE IS NOT NULL AND ? BETWEEN 400 AND 599 THEN ?
WHEN ID = ? AND CONTENT_TITLE IS NULL AND ? BETWEEN 400 AND 599 OR ID = ? AND CONTENT_TITLE = '' AND ? BETWEEN 400 AND 599 THEN ?
SET CONTENT_DSCR =
CASE
WHEN ID = ? AND CONTENT_DSCR IS NULL AND ? NOT BETWEEN 400 AND 599 OR ID = ? AND CONTENT_DSCR = '' AND ? NOT BETWEEN 400 AND 599 THEN ?
WHEN ID = ? AND CONTENT_TITLE IS NOT NULL AND ? BETWEEN 400 AND 599 THEN ?
SET SYSTEM_UPDATE_REASON = 'title and dscr update'
SET UPDATED_ON = SYSDATE
END

以下是我正在使用的占位符(？(：

u.urlId, 'title and dscr update', u.statusCode, u.urlId, 'title and dscr update', 
u.statusCode, u.contentTitle, u.urlId, u.statusCode, u.urlId, u.statusCode, 
u.contentTitle, u.urlId, u.statusCode, null, null, u.urlId, u.statusCode, u.urlId, 
u.statusCode, u.contentDescription, u.urlId, u.statusCode, 'title and dscr update'

Answer 1

也许这根本没有帮助，但让我试试(因为小时没有回复(：

我不了解DB2；如果它的SQL与Oracle的类似，那么

• CASE应该有其END
• 在整个UPDATE语句中只有一个SET关键字
• 从逻辑上讲，当有OR时，应该使用括号(否则会得到意外的结果(

类似这样的东西：

UPDATE URL
SET CONTENT_TITLE =
CASE
WHEN     (ID = ? AND SYSTEM_UPDATE_REASON = ?  AND CONTENT_TITLE IS NOT NULL AND ? NOT BETWEEN 400 AND 599) 
OR (ID = ? AND SYSTEM_UPDATE_REASON = ?  AND CONTENT_TITLE != ''       AND ? NOT BETWEEN 400 AND 599) THEN ?
WHEN     (ID = ? AND CONTENT_TITLE IS NULL     AND ? NOT BETWEEN 400 AND 599) 
OR (ID = ? AND CONTENT_TITLE = ''        AND ? NOT BETWEEN 400 AND 599) THEN ?
WHEN      ID = ? AND CONTENT_TITLE IS NOT NULL AND ? BETWEEN 400 AND 599 THEN ?
WHEN     (ID = ? AND CONTENT_TITLE IS NULL AND ? BETWEEN 400 AND 599) 
OR (ID = ? AND CONTENT_TITLE = ''    AND ? BETWEEN 400 AND 599) THEN ?
END,           
CONTENT_DSCR =
CASE
WHEN    (ID = ? AND CONTENT_DSCR IS NULL AND ? NOT BETWEEN 400 AND 599) 
OR (ID = ? AND CONTENT_DSCR = ''    AND ? NOT BETWEEN 400 AND 599) THEN ?
WHEN ID = ? AND CONTENT_TITLE IS NOT NULL AND ? BETWEEN 400 AND 599 THEN ?
END,     
SYSTEM_UPDATE_REASON = 'title and dscr update',
UPDATED_ON = SYSDATE