36 lines
1.2 KiB
Transact-SQL
36 lines
1.2 KiB
Transact-SQL
--Migration
|
|
UPDATE es SET
|
|
es.TOROLT = 'T'
|
|
,es.MODIFIER = 0
|
|
,es.LASTCHANGED = GETDATE()
|
|
FROM T_TANULOESEMENY es
|
|
INNER JOIN (
|
|
SELECT te.ID, ROW_NUMBER() OVER(PARTITION BY tte.C_TANULOID, te.C_TANITASIORAID, te.C_TIPUS ORDER BY te.CREATED DESC) RN
|
|
FROM T_TANULO_TANULOESEMENY tte
|
|
INNER JOIN T_TANULOESEMENY te ON tte.C_TANULOESEMENYID = te.ID
|
|
-- INNER JOIN T_TANEV tv ON tv.C_AKTIV = 'T' AND tv.ID = te.C_TANEVID
|
|
WHERE te.C_TANITASIORAID IS NOT NULL
|
|
AND te.TOROLT = 'F'
|
|
) x ON x.ID = es.ID
|
|
WHERE x.RN > 1
|
|
GO
|
|
|
|
UPDATE es SET
|
|
es.C_BEIRASTANULOID = tte.C_TANULOID
|
|
,es.MODIFIER = 0
|
|
,es.LASTCHANGED = GETDATE()
|
|
FROM T_TANULOESEMENY es
|
|
INNER JOIN T_TANULO_TANULOESEMENY tte ON tte.C_TANULOESEMENYID = es.ID
|
|
WHERE es.C_TANITASIORAID IS NOT NULL
|
|
AND es.TOROLT = 'F'
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCU_TanuloEsemeny_TanitasiOraId_Tipus_BeirasTanuloId') BEGIN
|
|
CREATE UNIQUE NONCLUSTERED INDEX NCU_TanuloEsemeny_TanitasiOraId_Tipus_BeirasTanuloId
|
|
ON dbo.T_TANULOESEMENY (C_TANITASIORAID, C_TIPUS, C_BEIRASTANULOID)
|
|
WHERE C_TANITASIORAID IS NOT NULL
|
|
AND C_BEIRASTANULOID IS NOT NULL
|
|
AND TOROLT = 'F'
|
|
WITH (FILLFACTOR = 80)
|
|
END
|
|
GO
|