修改法定编号

来源：互联网发布：淘宝店铺营销中心在哪编辑：程序博客网时间：2024/05/03 13:17

先将数据导入至UD表，然后执行SQL更新。

update PUB.GLJrnHed SET LegalNumber='UPDATE'
WHERE EXISTS (SELECT 1 FROM PUB.UD07 WHERE UD07.Key3=GLJrnHed.JournalCode AND UD07.Number01=GLJrnHed.JournalNum
AND UD07.Number02=GLJrnHed.FiscalYear AND UD07.Number03=GLJrnHed.FiscalPeriod )

update PUB.GLJrnHed SET (LegalNumber)=
(SELECT key1 FROM (SELECT DISTINCT key1,key3,number01,number02,number03 FROM PUB.UD07) aaa WHERE aaa.Key3=GLJrnHed.JournalCode AND aaa.Number01=GLJrnHed.JournalNum
AND aaa.Number02=GLJrnHed.FiscalYear AND aaa.Number03=GLJrnHed.FiscalPeriod )
WHERE LegalNumber='UPDATE'

update PUB.GLJrnDtl SET LegalNumber='UPDATE'
WHERE EXISTS (SELECT 1 FROM PUB.UD07 WHERE UD07.Key3=GLJrnDtl.JournalCode AND UD07.Number01=GLJrnDtl.JournalNum
AND UD07.Number02=GLJrnDtl.FiscalYear AND UD07.Number03=GLJrnDtl.FiscalPeriod AND UD07.Number04=GLJrnDtl.JournalLine)

update PUB.GLJrnDtl SET (LegalNumber)=
(SELECT key1 FROM PUB.UD07 WHERE UD07.Key3=GLJrnDtl.JournalCode AND UD07.Number01=GLJrnDtl.JournalNum
AND UD07.Number02=GLJrnDtl.FiscalYear AND UD07.Number03=GLJrnDtl.FiscalPeriod AND UD07.Number04=GLJrnDtl.JournalLine)
WHERE LegalNumber='UPDATE'

update PUB.GLJrnDtlMnl SET LegalNumber='UPDATE'
WHERE EXISTS (SELECT 1 FROM PUB.UD07 WHERE UD07.Key3=GLJrnDtlMnl.JournalCode AND UD07.Number01=GLJrnDtlMnl.JournalNum
AND UD07.Number02=GLJrnDtlMnl.FiscalYear AND UD07.Number03=GLJrnDtlMnl.FiscalPeriod AND UD07.Number04=GLJrnDtlMnl.JournalLine)

update PUB.GLJrnDtlMnl SET (LegalNumber)=
(SELECT key1 FROM PUB.UD07 WHERE UD07.Key3=GLJrnDtlMnl.JournalCode AND UD07.Number01=GLJrnDtlMnl.JournalNum
AND UD07.Number02=GLJrnDtlMnl.FiscalYear AND UD07.Number03=GLJrnDtlMnl.FiscalPeriod AND UD07.Number04=GLJrnDtlMnl.JournalLine)
WHERE LegalNumber='UPDATE'