sql - 在 Access 中同时进行多个更新

Question

我对 Access 的工作原理知之甚少，但我需要一些比我现在正在做的更有效的方法。

我有这些疑问:

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #04/07/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#4/7/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #2/27/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#2/27/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] =#5/29/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#5/29/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] =#8/25/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#8/25/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/28/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#8/28/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #9/29/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#9/29/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #2/25/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#2/25/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #3/30/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#3/30/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/23/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/23/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/25/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/25/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/26/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/26/404#));


UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/27/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/27/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/30/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/30/404#));

问题是我必须单独运行它们。有没有一种方法可以将它们组合成一个查询？任何帮助表示赞赏。

Answer 1

也许像这样？

UPDATE [Receipt Audit]
SET [Receipt Date] = DateSerial(Switch(Year([Receipt Date])=303,2003,Year([Receipt Date])=404,2004,True,Year([Receipt Date])),Month([Receipt Date]),Day([Receipt Date]))
WHERE Year([Receipt Date]) IN (303,404)