Metode I.
DECLARE @OldNIP VARCHAR(50) = 'NIP_LAMA';
DECLARE @NewNIP VARCHAR(50) = 'NIP_BARU';
DECLARE @TableName NVARCHAR(256);
DECLARE @Sql NVARCHAR(MAX);
-- Cursor untuk mencari semua table yang memiliki kolom 'NIP'
DECLARE curTables CURSOR FOR
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NIP';
OPEN curTables;
FETCH NEXT FROM curTables INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Membuat query UPDATE secara dinamis
SET @Sql = N'UPDATE ' + QUOTENAME(@TableName) +
N' SET NIP = @NewNipValue WHERE NIP = @OldNipValue;';
-- Menjalankan query
EXEC sp_executesql @Sql,
N'@OldNipValue VARCHAR(50), @NewNipValue VARCHAR(50)',
@OldNIP, @NewNIP;
FETCH NEXT FROM curTables INTO @TableName;
END
CLOSE curTables;
DEALLOCATE curTables;
Metode II.
BEGIN TRANSACTION;
BEGIN TRY
-- Update Table 1
UPDATE Tabel_Karyawan
SET NIP = 'NIP_BARU'
WHERE NIP = 'NIP_LAMA';
-- Update Table 2
UPDATE Tabel_Absensi
SET NIP = 'NIP_BARU'
WHERE NIP = 'NIP_LAMA';
-- Update Table 3
UPDATE Tabel_Gaji
SET NIP = 'NIP_BARU'
WHERE NIP = 'NIP_LAMA';
-- Simpan perubahan jika semua sukses
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Batalkan semua perubahan jika terjadi error
ROLLBACK TRANSACTION;
PRINT 'Error terjadi, transaksi dibatalkan.';
END CATCH;
