SQL更新语句求助：基于关联表B的EVENT字段状态更新表A的NEWCOLUMN值

Looks like I spotted the immediate issue with your query first—you got the column name wrong for table A! Table A has ARTICLE_NUMBER (with an underscore), but your JOIN condition uses a.ARTICLENUMBER (no underscore). That mismatch means your join isn't matching any rows at all, so no updates happen. Let's fix that first, then cover the rest.

Fixing the Core Issue & Basic Update (for MySQL/MariaDB) #

First, correct the column name in the JOIN condition. If you only want to update rows in A that have a matching entry in B, use an INNER JOIN:

UPDATE A a
INNER JOIN B b ON a.ARTICLE_NUMBER = b.ARTICLENUMBER
SET a.NEWCOLUMN = CASE 
    WHEN b.EVENT IS NULL THEN 0 
    ELSE 1 
END;

Updating All Rows in A (Including Those Without a Match in B) #

If you want to set NEWCOLUMN to 0 for rows in A that don't have any corresponding entry in B (not just ones where B's EVENT is NULL), switch to a LEFT JOIN:

UPDATE A a
LEFT JOIN B b ON a.ARTICLE_NUMBER = b.ARTICLENUMBER
SET a.NEWCOLUMN = CASE 
    WHEN b.EVENT IS NULL THEN 0 
    ELSE 1 
END;

This works because for rows in A with no match in B, b.EVENT will be NULL, so they'll get set to 0 automatically.

Syntax for Other Databases #

If you're using SQL Server instead, the UPDATE JOIN syntax is a bit different:

UPDATE a
SET a.NEWCOLUMN = CASE 
    WHEN b.EVENT IS NULL THEN 0 
    ELSE 1 
END
FROM A a
LEFT JOIN B b ON a.ARTICLE_NUMBER = b.ARTICLENUMBER;

For Oracle, since it doesn't support UPDATE with JOIN directly, use a MERGE statement:

MERGE INTO A a
USING B b ON (a.ARTICLE_NUMBER = b.ARTICLENUMBER)
WHEN MATCHED THEN
    UPDATE SET a.NEWCOLUMN = CASE 
        WHEN b.EVENT IS NULL THEN 0 
        ELSE 1 
    END
WHEN NOT MATCHED THEN
    UPDATE SET a.NEWCOLUMN = 0; -- Handles rows in A with no match in B

Quick Simplification Note #

You can shorten the CASE statement in most databases if you prefer. For example, in MySQL, you can use the IF() function:

SET a.NEWCOLUMN = IF(b.EVENT IS NULL, 0, 1)

Or in SQL Server, cast the boolean check to an integer:

SET a.NEWCOLUMN = CAST(NOT (b.EVENT IS NULL) AS INT)

The CASE statement is just the most universally compatible approach.

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