33 lines
No EOL
1.7 KiB
SQL
33 lines
No EOL
1.7 KiB
SQL
--Migration
|
|
-- Ahol a városnév csak négy számjegy (irányítószám)
|
|
UPDATE c SET C_VAROS = ts.C_TELEPULESNEV
|
|
,MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
|
|
FROM T_CIM c
|
|
INNER JOIN T_TANEV tv ON tv.ID = c.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENY i ON i.ID = tv.C_INTEZMENYID AND i.TOROLT = 'F'
|
|
INNER JOIN T_TELEPULES ts ON ts.C_IRANYITOSZAM = c.C_IRANYITOSZAM
|
|
WHERE C_VAROS LIKE '[0-9][0-9][0-9][0-9]' AND C_CIMTIPUSA = 907 AND c.TOROLT= 'F'
|
|
|
|
-- Irszám településnév javítása
|
|
UPDATE c SET C_VAROS = LTRIM(RTRIM(SUBSTRING(C_VAROS, 5, 120)))
|
|
,MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
|
|
FROM T_CIM c
|
|
INNER JOIN T_TANEV tv ON tv.ID = c.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENY i ON i.ID = tv.C_INTEZMENYID AND i.TOROLT = 'F'
|
|
WHERE C_VAROS LIKE '[0-9][0-9][0-9][0-9] _%' AND C_CIMTIPUSA = 907 AND c.TOROLT = 'F'
|
|
|
|
-- "Irszám, településnév" javítása
|
|
UPDATE c SET C_VAROS = LTRIM(RTRIM(SUBSTRING(C_VAROS, 6, 120)))
|
|
,MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
|
|
FROM T_CIM c
|
|
INNER JOIN T_TANEV tv ON tv.ID = c.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENY i ON i.ID = tv.C_INTEZMENYID AND i.TOROLT = 'F'
|
|
WHERE C_VAROS LIKE '[0-9][0-9][0-9][0-9], _%' AND C_CIMTIPUSA = 907 AND c.TOROLT = 'F'
|
|
|
|
-- településnév (irszám)
|
|
UPDATE c SET C_VAROS = LTRIM(RTRIM(SUBSTRING(C_VAROS, 1, LEN(C_VAROS) - 6)))
|
|
,MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
|
|
FROM T_CIM c
|
|
INNER JOIN T_TANEV tv ON tv.ID = c.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENY i ON i.ID = tv.C_INTEZMENYID AND i.TOROLT = 'F'
|
|
WHERE C_VAROS LIKE '%_([0-9][0-9][0-9][0-9])' AND C_CIMTIPUSA = 907 AND c.TOROLT = 'F' |