基于日期条件通过UTILITYREADING表更新UTILITYINVOICE表ELECPREVIOUS字段失败的技术求助

Hey there! The issue with your current UPDATE query is that the subquery fetching the previous month's reading doesn't properly link to the UTILITYINVOICE table, and it also lacks precise filters for the previous month. Let's break this down and fix it.

What's Wrong with Your Current Query? #

Your subquery SELECT NVL(ELECTRICITYREADING, 0) FROM UTILITYREADING doesn't specify which room's reading to pull, nor does it narrow down to the previous month. This would either throw an error (if multiple rows exist) or return a random reading—definitely not what you need. The EXISTS clause checks if a previous reading exists, but the actual value assignment isn't tied to that specific room's data.

The Corrected UPDATE Query #

Here's a query that properly connects each room in UTILITYINVOICE to its corresponding previous month's reading in UTILITYREADING, and handles edge cases like missing readings or year-end month transitions:

UPDATE UTILITYINVOICE ui
SET ELECPREVIOUS = (
    SELECT NVL(ur.ELECTRICITYREADING, 0)
    FROM UTILITYREADING ur
    WHERE ur.ROOMNUMBER = ui.ROOMNUMBER
      AND ur.DATEOFREADING >= ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1)
      AND ur.DATEOFREADING < TRUNC(SYSDATE, 'MONTH')
)
WHERE EXISTS (
    SELECT 1
    FROM UTILITYREADING ur
    WHERE ur.ROOMNUMBER = ui.ROOMNUMBER
      AND ur.DATEOFREADING >= ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1)
      AND ur.DATEOFREADING < TRUNC(SYSDATE, 'MONTH')
);

Key Improvements Explained: #

• Table Aliases: Using ui for UTILITYINVOICE and ur for UTILITYREADING makes the query cleaner and explicitly links the two tables via ur.ROOMNUMBER = ui.ROOMNUMBER, ensuring we get the reading for the exact room we're updating.
• Safe Date Filtering: Instead of subtracting 1 from the current month (which breaks for January, since 1-1=0), we use ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1) to get the start of the previous month, and TRUNC(SYSDATE, 'MONTH') as the start of the current month. This safely handles year transitions (e.g., January's previous month is December of the prior year).
• Targeted Value Assignment: The subquery now only fetches the reading for the room being updated, so each ELECPREVIOUS value matches the correct room's previous month reading.

Testing with Your Sample Data #

For your sample UTILITYREADING data:

• N201 has a January 2022 reading of 240.6
• N202 has a January 2022 reading of 259.8

Running this query will correctly set ELECPREVIOUS to those values for each room in UTILITYINVOICE, matching your target structure perfectly.

内容的提问来源于stack exchange，提问作者lee