53 lines
1.8 KiB
Transact-SQL
53 lines
1.8 KiB
Transact-SQL
--GUID AZONOS TANÉVEKEN ÁTÍVELŐEN
|
|
update [dbo].T_FELHASZNALOBELEPES
|
|
SET
|
|
C_GUID = userneedupdate.newGuid
|
|
FROM
|
|
(
|
|
select
|
|
new.LoginName as newLoginName,
|
|
new.[Guid] as newGuid,
|
|
new.InstituteId as newAndOldInstituteId
|
|
from
|
|
(
|
|
SELECT
|
|
felhasznalobelepes.C_GUID AS [Guid],
|
|
felhasznalobelepes.ID AS UserId,
|
|
intezmeny.ID AS InstituteId,
|
|
tanev.ID AS tanevId,
|
|
felhasznalobelepes.C_BEJELENTKEZESINEV AS LoginName
|
|
FROM
|
|
[dbo].T_FELHASZNALOBELEPES AS felhasznalobelepes
|
|
INNER JOIN [dbo].T_INTEZMENY intezmeny ON intezmeny.id = felhasznalobelepes.C_INTEZMENYID
|
|
INNER JOIN [dbo].T_TANEV AS tanev ON felhasznalobelepes.C_TANEVID = tanev.ID AND felhasznalobelepes.C_INTEZMENYID = tanev.C_INTEZMENYID AND tanev.TOROLT='F' AND tanev.C_AKTIV='T'
|
|
WHERE
|
|
felhasznalobelepes.TOROLT = 'F'
|
|
) AS new
|
|
INNER JOIN
|
|
( SELECT
|
|
felhasznalobelepes.C_GUID AS [Guid],
|
|
felhasznalobelepes.ID AS UserId,
|
|
intezmeny.ID AS InstituteId,
|
|
tanev.ID AS tanevId,
|
|
felhasznalobelepes.C_BEJELENTKEZESINEV AS LoginName
|
|
FROM
|
|
[dbo].T_FELHASZNALOBELEPES AS felhasznalobelepes
|
|
INNER JOIN [dbo].T_INTEZMENY intezmeny ON intezmeny.id = felhasznalobelepes.C_INTEZMENYID
|
|
INNER JOIN [dbo].T_TANEV AS tanev ON felhasznalobelepes.C_TANEVID = tanev.ID AND felhasznalobelepes.C_INTEZMENYID = tanev.C_INTEZMENYID AND tanev.C_AKTIV='F'
|
|
WHERE
|
|
felhasznalobelepes.TOROLT = 'F') as old
|
|
ON new.LoginName = old.LoginName AND new.InstituteId = old.InstituteId
|
|
WHERE new.[Guid] <> old.[Guid]
|
|
) as userneedupdate
|
|
|
|
WHERE
|
|
C_BEJELENTKEZESINEV = userneedupdate.newLoginName AND
|
|
C_INTEZMENYID = userneedupdate.newAndOldInstituteId
|
|
|
|
--NEM AKTÍV TANÉVES USER-EK TÖRLÉSE
|
|
UPDATE fb
|
|
SET
|
|
fb.TOROLT = 'T'
|
|
FROM [dbo].T_FELHASZNALOBELEPES AS fb
|
|
INNER JOIN [dbo].T_TANEV AS tanev ON fb.C_TANEVID = tanev.ID
|
|
WHERE tanev.C_AKTIV = 'F' |