Mengubah field NIP secara langsung pada beberapa table sekaligus di SQL Server,

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;