sql server MERGE INTO 同一个表，报错

Question

MERGE INTO FACTORY_STATISTICS f USING ( SELECT COUNT ( 1 ) AS COUNT FROM FACTORY_STATISTICS ff WHERE ff.FACTORYCODE = '1000' AND ff.MATERIALID = 'T01100' ) CONDITION ON ( CONDITION.count > 0 ) 
WHEN MATCHED THEN
UPDATE 
    SET f.PARTAVANUM = f.PARTAVANUM + 1,
    f.MATCHAVANUM = f.MATCHAVANUM + 1,
    f.AVAILABLENUM = f.AVAILABLENUM + 1,
    f.TOTALNUM = f.TOTALNUM + 1 
    WHEN NOT MATCHED 
    AND f.FACTORYCODE = '1000' 
    AND f.MATERIALID = 'T01100' THEN
    INSERT (
    f.ID,
    f.FACTORYCODE,
    f.MATERIALID,
    f.PARTAVANUM,
    f.PARTFREEZENUM,
    f.PARTLOCKNUM,
    f.MATCHAVANUM,
    f.MATCHFREEZENUM,
    f.MATCHLOCKNUM,
    f.AVAILABLENUM,
    f.FREEZENUM,
    f.LOCKNUM,
    f.TotalNum 
    )
VALUES
    (
    '1',
    '1',
    '1',
    '1',
    '1',
    '1',
    '1',
    '1',
    '1',
    '1',
    '1',
    '1',
    '1' 
    )
```[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]MERGE 语句中使用的插入列列表中不能包含多部分标识符。请改用单部分标识符。 (10739)

Answer 1

1、目标表字段不能作为条件判断

WHEN NOT MATCHED  AND f.FACTORYCODE = '1000'   AND f.MATERIALID = 'T01100' THEN

2、你INSERT 语句中行值表达式的数目超出了设置 行值的最大允许值。
3、更新同一张表，完全没必要merge，再者你的f和ff并无关系，没有任何匹配和不匹配可言。