kreta/Kreta.DataAccess.Migrations/Scripts/20220218142759_KRETA2_11387/KRETA2_11387.sql
2024-03-13 00:33:46 +01:00

25 lines
995 B
SQL

UPDATE up
SET
up.C_TANEVID = f.C_TANEVID
,up.MODIFIER = 0
,up.LASTCHANGED = GETDATE()
,up.SERIAL += 1
FROM T_USERPROFILE up
INNER JOIN T_FELHASZNALO f ON f.ID = up.C_FELHASZNALOID
INNER JOIN T_TANEV tn ON tn.ID = up.C_TANEVID AND tn.TOROLT = 'F'
INNER JOIN T_INTEZMENY i ON i.ID = up.C_INTEZMENYID AND i.TOROLT = 'F'
WHERE up.C_TANEVID <> f.C_TANEVID
AND NOT EXISTS (SELECT 1 FROM T_USERPROFILE up2 WHERE up2.C_FELHASZNALOID = f.ID AND up2.C_TANEVID = f.C_TANEVID AND up2.TOROLT = 'F')
UPDATE up
SET
up.TOROLT = 'F'
,up.MODIFIER = 0
,up.LASTCHANGED = GETDATE()
,up.SERIAL += 1
FROM T_USERPROFILE up
INNER JOIN T_FELHASZNALO f ON f.ID = up.C_FELHASZNALOID
INNER JOIN T_TANEV tn ON tn.ID = up.C_TANEVID AND tn.TOROLT = 'F'
INNER JOIN T_INTEZMENY i ON i.ID = up.C_INTEZMENYID AND i.TOROLT = 'F'
WHERE up.C_TANEVID <> f.C_TANEVID
AND EXISTS (SELECT 1 FROM T_USERPROFILE up2 WHERE up2.C_FELHASZNALOID = f.ID AND up2.C_TANEVID = f.C_TANEVID AND up2.TOROLT = 'F')