UPDATE (Transact-SQL)
Berlaku untuk:
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Warehouse di Microsoft Fabric
Mengubah data yang ada dalam tabel atau tampilan di SQL Server. Misalnya, lihat Contoh.
Sintaks
-- Syntax for SQL Server and Azure SQL Database
[ WITH <common_table_expression> [...n] ]
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
{ { table_alias | <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
| @table_variable
}
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] )
}
}
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
| @variable = column = expression
| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
} [ ,...n ]
[ <OUTPUT Clause> ]
[ FROM{ <table_source> } [ ,...n ] ]
[ WHERE { <search_condition>
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
}
]
[ OPTION ( <query_hint> [ ,...n ] ) ]
[ ; ]
<object> ::=
{
[ server_name . database_name . schema_name .
| database_name .[ schema_name ] .
| schema_name .
]
table_or_view_name}
-- Syntax for Azure Synapse Analytics and Microsoft Fabric
[ WITH <common_table_expression> [ ,...n ] ]
UPDATE [ database_name . [ schema_name ] . | schema_name . ] table_name
SET { column_name = { expression | NULL } } [ ,...n ]
FROM [ database_name . [ schema_name ] . | schema_name . ] table_name
JOIN {<join_table_source>}[ ,...n ]
ON <join_condition>
[ WHERE <search_condition> ]
[ OPTION ( LABEL = label_name ) ]
[;]
<join_table_source> ::=
{
[ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias
[ <tablesample_clause>]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
}
-- Syntax for Parallel Data Warehouse
UPDATE [ database_name . [ schema_name ] . | schema_name . ] table_name
SET { column_name = { expression | NULL } } [ ,...n ]
[ FROM from_clause ]
[ WHERE <search_condition> ]
[ OPTION ( LABEL = label_name ) ]
[;]
Argumen
WITH <common_table_expression>
Menentukan kumpulan atau tampilan hasil bernama sementara, juga dikenal sebagai ekspresi tabel umum (CTE), yang ditentukan dalam cakupan pernyataan UPDATE. Kumpulan hasil CTE berasal dari kueri sederhana dan dirujuk oleh pernyataan UPDATE.
Ekspresi tabel umum juga dapat digunakan dengan pernyataan SELECT, INSERT, DELETE, dan CREATE VIEW. Untuk informasi selengkapnya, lihat WITH common_table_expression (Transact-SQL).
TOP ( ekspresi) [ PERCENT ]
Menentukan jumlah atau persentase baris yang diperbarui. ekspresi dapat berupa angka atau persentase baris.
Baris yang direferensikan dalam ekspresi TOP yang digunakan dengan INSERT, UPDATE, atau DELETE tidak diatur dalam urutan apa pun.
Ekspresi pembatas tanda kurung di TOP diperlukan dalam pernyataan INSERT, UPDATE, dan DELETE. Untuk informasi selengkapnya, lihat TOP (Transact-SQL).
table_alias
Alias yang ditentukan dalam klausa UPDATE yang mewakili tabel atau tampilan tempat baris akan diperbarui.
server_name
Adalah nama server (menggunakan nama server tertaut atau fungsi OPENDATASOURCE sebagai nama server) tempat tabel atau tampilan berada. Jika server_name ditentukan, database_name dan schema_name diperlukan.
database_name
Adalah nama database.
schema_name
Adalah nama skema tempat tabel atau tampilan berada.
table_or_view_name
Adalah nama tabel atau tampilan tempat baris akan diperbarui. Tampilan yang dirujuk oleh table_or_view_name harus dapat diperbarui dan mereferensikan tepat satu tabel dasar dalam klausa FROM tampilan. Untuk informasi selengkapnya tentang tampilan yang dapat diperbarui, lihat CREATE VIEW (Transact-SQL).
rowset_function_limited
Apakah fungsi OPENQUERY atau OPENROWSET, tunduk pada kemampuan penyedia.
WITH (<Table_Hint_Limited>)
Menentukan satu atau beberapa petunjuk tabel yang diizinkan untuk tabel target. Kata kunci WITH dan tanda kurung diperlukan. NOLOCK, READUNCOMMITTED, NOEXPAND, dan beberapa lainnya tidak diizinkan. Untuk informasi tentang petunjuk tabel, lihat Petunjuk Tabel (Transact-SQL).
@table_variable
Menentukan variabel tabel sebagai sumber tabel.
SET
Menentukan daftar nama kolom atau variabel yang akan diperbarui.
column_name
Adalah kolom yang berisi data yang akan diubah. column_name harus ada di table_or view_name. Kolom identitas tidak dapat diperbarui.
expression
Adalah variabel, nilai harfiah, ekspresi, atau pernyataan subpilih (diapit dengan tanda kurung) yang mengembalikan satu nilai. Nilai yang dikembalikan oleh ekspresi menggantikan nilai yang ada di column_name atau @variabel.
Catatan
Saat mereferensikan jenis data karakter Unicode nchar, nvarchar, dan ntext, 'ekspresi' harus diawali dengan huruf kapital 'N'. Jika 'N' tidak ditentukan, SQL Server mengonversi string ke halaman kode yang sesuai dengan kolase default database atau kolom. Karakter apa pun yang tidak ditemukan di halaman kode ini hilang.
DEFAULT
Menentukan bahwa nilai default yang ditentukan untuk kolom adalah menggantikan nilai yang ada di kolom. Ini juga dapat digunakan untuk mengubah kolom menjadi NULL jika kolom tidak memiliki default dan didefinisikan untuk mengizinkan nilai null.
{ +=*=-= | | | /=%= | | &= } | ^= | |=
Operator penetapan gabungan:
+= Tambahkan dan tetapkan
-= Kurangi dan tetapkan
*= Kalikan dan tetapkan
/= Bagi dan tetapkan
%= Modulo dan tetapkan
&= Bitwise DAN dan tetapkan
^= Bitwise XOR dan tetapkan
|= Bitwise OR dan tetapkan
udt_column_name
Adalah kolom jenis yang ditentukan pengguna.
| field_name property_name
Adalah properti publik atau anggota data publik dari jenis yang ditentukan pengguna.
method_name ( argumen [ , , ... n] )
Adalah metode mutator publik nonstatis dari udt_column_name yang mengambil satu atau beberapa argumen.
.WRITE (ekspresi,Offset,Length@@)
Menentukan bahwa bagian dari nilai column_name akan dimodifikasi. ekspresi menggantikan unit @Panjang mulai dari @Offset column_name. Hanya kolom varchar(max), nvarchar(max), atau varbinary(max) yang dapat ditentukan dengan klausa ini. column_name tidak boleh NULL dan tidak dapat memenuhi syarat dengan nama tabel atau alias tabel.
ekspresi adalah nilai yang disalin ke column_name. ekspresi harus mengevaluasi ke atau dapat secara implisit ditransmisikan ke jenis column_name . Jika ekspresi diatur ke NULL, @Length diabaikan, dan nilai dalam column_name dipotong pada @Offset yang ditentukan.
@Offset adalah titik awal dalam nilai yang disimpan dalam column_name di mana ekspresi ditulis. @Offset adalah posisi byte ordinal berbasis nol, besar, dan tidak dapat berupa angka negatif. Jika @Offset NULL, operasi pembaruan menambahkan ekspresi di akhir nilai column_name yang ada dan @Length diabaikan. Jika @Offset lebih besar dari panjang byte dari nilai column_name , Mesin Database mengembalikan kesalahan. Jika @Offset plus @Length melebihi akhir nilai yang mendasar di kolom, penghapusan terjadi hingga karakter terakhir dari nilai.
@Panjang adalah panjang bagian dalam kolom, mulai dari @Offset, yang digantikan oleh ekspresi. @Panjangnya besar dan tidak boleh berupa angka negatif. Jika @Length NULL, operasi pembaruan akan menghapus semua data dari @Offset ke akhir nilai column_name .
Untuk informasi selengkapnya, lihat Memperbarui Jenis Data Nilai Besar.
@variabel
Adalah variabel yang dideklarasikan yang diatur ke nilai yang dikembalikan oleh ekspresi.
Ekspresi kolom = variabel = SET @mengatur variabel ke nilai yang sama dengan kolom . Ini berbeda dari kolom variabel = SET@, ekspresi kolom = , yang mengatur variabel ke nilai pra-pembaruan kolom.
<OUTPUT_Clause>
Mengembalikan data atau ekspresi yang diperbarui berdasarkan data atau ekspresi tersebut sebagai bagian dari operasi UPDATE. Klausa OUTPUT tidak didukung dalam pernyataan DML apa pun yang menargetkan tabel atau tampilan jarak jauh. Untuk informasi selengkapnya tentang argumen dan perilaku klausa ini, lihat Klausul OUTPUT (Transact-SQL).
DARI <table_source>
Menentukan bahwa tabel, tampilan, atau sumber tabel turunan digunakan untuk memberikan kriteria untuk operasi pembaruan. Untuk informasi selengkapnya, lihat FROM (Transact-SQL).
Jika objek yang diperbarui sama dengan objek dalam klausa FROM dan hanya ada satu referensi ke objek dalam klausa FROM, alias objek mungkin atau mungkin tidak ditentukan. Jika objek yang diperbarui muncul lebih dari satu kali dalam klausa FROM, satu, dan hanya satu, referensi ke objek tidak boleh menentukan alias tabel. Semua referensi lain ke objek dalam klausul FROM harus menyertakan alias objek.
Tampilan dengan pemicu ALIH-ALIH PEMBARUAN tidak dapat menjadi target PEMBARUAN dengan klausa FROM.
Catatan
Setiap panggilan ke OPENDATASOURCE, OPENQUERY, atau OPENROWSET dalam klausul FROM dievaluasi secara terpisah dan independen dari panggilan apa pun ke fungsi-fungsi ini yang digunakan sebagai target pembaruan, bahkan jika argumen identik diberikan ke dua panggilan. Secara khusus, kondisi filter atau gabungan yang diterapkan pada hasil salah satu panggilan tersebut tidak berpengaruh pada hasil yang lain.
WHERE
Menentukan kondisi yang membatasi baris yang diperbarui. Ada dua bentuk pembaruan berdasarkan bentuk klausa WHERE mana yang digunakan:
Pembaruan yang dicari menentukan kondisi pencarian untuk memenuhi syarat baris yang akan dihapus.
Pembaruan yang diposisikan menggunakan klausa CURRENT OF untuk menentukan kursor. Operasi pembaruan terjadi pada posisi kursor saat ini.
<search_condition>
Menentukan kondisi yang akan dipenuhi agar baris diperbarui. Kondisi pencarian juga dapat menjadi kondisi di mana gabungan didasarkan. Tidak ada batasan jumlah predikat yang dapat disertakan dalam kondisi pencarian. Untuk informasi selengkapnya tentang predikat dan kondisi pencarian, lihat Kondisi Pencarian (Transact-SQL).
SAAT INI DARI
Menentukan bahwa pembaruan dilakukan pada posisi kursor yang ditentukan saat ini.
Pembaruan yang diposisikan menggunakan klausa WHERE CURRENT OF memperbarui baris tunggal pada posisi kursor saat ini. Ini bisa lebih akurat daripada pembaruan yang dicari yang menggunakan klausul WHERE <search_condition> untuk memenuhi syarat baris yang akan diperbarui. Pembaruan yang dicari memodifikasi beberapa baris saat kondisi pencarian tidak secara unik mengidentifikasi satu baris.
GLOBAL
Menentukan bahwa cursor_name mengacu pada kursor global.
cursor_name
Adalah nama kursor terbuka tempat pengambilan harus dibuat. Jika kursor global dan lokal dengan nama cursor_name ada, argumen ini mengacu pada kursor global jika GLOBAL ditentukan; jika tidak, itu mengacu pada kursor lokal. Kursor harus memperbolehkan pembaruan.
cursor_variable_name
Adalah nama variabel kursor. cursor_variable_name harus mereferensikan kursor yang memungkinkan pembaruan.
OPTION (<query_hint> [ , ...n ] )
Menentukan bahwa petunjuk pengoptimal digunakan untuk menyesuaikan cara Mesin Database memproses pernyataan. Untuk informasi selengkapnya, lihat Petunjuk Kueri (Transact-SQL).
Praktik terbaik
@@ROWCOUNT Gunakan fungsi untuk mengembalikan jumlah baris yang disisipkan ke aplikasi klien. Untuk informasi selengkapnya, lihat @@ROWCOUNT (Transact-SQL).
Nama variabel dapat digunakan dalam pernyataan UPDATE untuk menampilkan nilai lama dan baru yang terpengaruh, tetapi ini harus digunakan hanya ketika pernyataan UPDATE memengaruhi satu rekaman. Jika pernyataan UPDATE memengaruhi beberapa rekaman, untuk mengembalikan nilai lama dan baru untuk setiap rekaman, gunakan klausa OUTPUT.
Berhati-hatilah saat menentukan klausul FROM untuk memberikan kriteria untuk operasi pembaruan. Hasil pernyataan UPDATE tidak ditentukan jika pernyataan menyertakan klausul FROM yang tidak ditentukan sedemikian sehingga hanya satu nilai yang tersedia untuk setiap kemunculan kolom yang diperbarui, yaitu jika pernyataan UPDATE tidak deterministik. Misalnya, dalam pernyataan UPDATE dalam skrip berikut, kedua baris dalam Table1 memenuhi kualifikasi klausul FROM dalam pernyataan UPDATE; tetapi tidak ditentukan baris mana yang Table1 digunakan untuk memperbarui baris di Table2.
USE AdventureWorks2022;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1
(ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2
(ColA INT PRIMARY KEY NOT NULL, ColB DECIMAL(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0);
INSERT INTO dbo.Table2 VALUES(1, 0.0);
GO
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO
SELECT ColA, ColB
FROM dbo.Table2;
Masalah yang sama dapat terjadi ketika FROM klausa dan WHERE CURRENT OF digabungkan. Dalam contoh berikut, kedua baris dalam Table2 memenuhi kualifikasi klausul FROM dalam UPDATE pernyataan. Tidak ditentukan baris Table2 mana yang akan digunakan untuk memperbarui baris di Table1.
USE AdventureWorks2022;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1
(c1 INT PRIMARY KEY NOT NULL, c2 INT NOT NULL);
GO
CREATE TABLE dbo.Table2
(d1 INT PRIMARY KEY NOT NULL, d2 INT NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES (1, 10);
INSERT INTO dbo.Table2 VALUES (1, 20), (2, 30);
GO
DECLARE abc CURSOR LOCAL FOR
SELECT c1, c2
FROM dbo.Table1;
OPEN abc;
FETCH abc;
UPDATE dbo.Table1
SET c2 = c2 + d2
FROM dbo.Table2
WHERE CURRENT OF abc;
GO
SELECT c1, c2 FROM dbo.Table1;
GO
Dukungan kompatibilitas
Dukungan untuk penggunaan petunjuk READUNCOMMITTED dan NOLOCK dalam klausa FROM yang berlaku untuk tabel target pernyataan UPDATE atau DELETE akan dihapus dalam versi SQL Server mendatang. Hindari menggunakan petunjuk ini dalam konteks ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakannya.
Jenis data
Semua kolom karakter dan nchar diisi kanan ke panjang yang ditentukan.
Jika ANSI_PADDING diatur ke NONAKTIF, semua spasi berikutnya dihapus dari data yang dimasukkan ke dalam kolom varchar dan nvarchar , kecuali dalam string yang hanya berisi spasi. String ini dipotong menjadi string kosong. Jika ANSI_PADDING diatur ke AKTIF, spasi berikutnya akan disisipkan. Driver ODBC Microsoft SQL Server dan Penyedia OLE DB untuk SQL Server secara otomatis mengatur ANSI_PADDING AKTIF untuk setiap koneksi. Ini dapat dikonfigurasi di sumber data ODBC atau dengan mengatur atribut atau properti koneksi. Untuk informasi selengkapnya, lihat SET ANSI_PADDING (Transact-SQL).
Memperbarui kolom teks, ntext, dan gambar
Memodifikasi kolom teks, ntext, atau gambar dengan UPDATE menginisialisasi kolom, menetapkan penunjuk teks yang valid ke kolom tersebut, dan mengalokasikan setidaknya satu halaman data, kecuali kolom sedang diperbarui dengan NULL.
Untuk mengganti atau mengubah blok besar data teks, ntext, atau gambar , gunakan WRITETEXT atau UPDATETEXT alih-alih pernyataan UPDATE.
Jika pernyataan UPDATE dapat mengubah lebih dari satu baris saat memperbarui kunci pengklusteran dan satu atau beberapa kolom teks, ntext, atau gambar , pembaruan parsial ke kolom ini dijalankan sebagai pengganti penuh nilai.
Penting
Jenis data ntext, teks, dan gambar akan dihapus dalam versi Microsoft SQL Server yang akan datang. Hindari menggunakan jenis data ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakannya. Gunakan nvarchar(max), varchar(max), dan varbinary(max) sebagai gantinya.
Memperbarui jenis data nilai besar
Gunakan .Klausul WRITE (expression,Offset,Length@@) untuk melakukan pembaruan varchar(maks) parsial atau penuh, nvarchar(max), dan jenis data varbinary(max).
Misalnya, pembaruan parsial kolom varchar(maks) dapat menghapus atau memodifikasi hanya 200 byte pertama kolom (200 karakter jika menggunakan karakter ASCII), sedangkan pembaruan lengkap akan menghapus atau memodifikasi semua data di kolom. . Pembaruan WRITE yang menyisipkan atau menambahkan data baru dicatat minimal jika model pemulihan database diatur ke dicatat secara massal atau sederhana. Pengelogan minimal tidak digunakan ketika nilai yang ada diperbarui. Untuk informasi selengkapnya, lihat Log Transaksi (SQL Server).
Mesin Database mengonversi pembaruan parsial ke pembaruan penuh ketika pernyataan UPDATE menyebabkan salah satu tindakan ini:
- Mengubah kolom kunci tampilan atau tabel yang dipartisi.
- Memodifikasi lebih dari satu baris dan juga memperbarui kunci indeks berkluster nonunique ke nilai nonkonstant.
Anda tidak dapat menggunakan . Klausa WRITE untuk memperbarui kolom NULL atau mengatur nilai column_name ke NULL.
@Offset dan @Length ditentukan dalam byte untuk jenis data varbinary dan varchar dan dalam byte-pairs untuk jenis data nvarchar . Untuk informasi selengkapnya tentang panjang jenis data string, lihat char dan varchar (Transact-SQL) dan nchar dan nvarchar (Transact-SQL).
Untuk performa terbaik, sebaiknya data dimasukkan atau diperbarui dalam ukuran gugus yang merupakan kelipatan 8040 byte.
Jika kolom dimodifikasi oleh . Klausa WRITE dirujuk dalam klausa OUTPUT, nilai lengkap kolom, baik gambar sebelum dalam dihapus. column_name atau gambar setelah disisipkan.column_name, dikembalikan ke kolom yang ditentukan dalam variabel tabel. Lihat contoh R yang mengikuti.
Untuk mencapai fungsionalitas yang sama dari . TULIS dengan karakter lain atau jenis data biner, gunakan STUFF (Transact-SQL).
Memperbarui kolom Tipe yang Ditentukan Pengguna
Memperbarui nilai dalam kolom jenis yang ditentukan pengguna dapat dicapai dengan salah satu cara berikut:
Menyediakan nilai dalam jenis data sistem SQL Server, selama jenis yang ditentukan pengguna mendukung konversi implisit atau eksplisit dari jenis tersebut. Contoh berikut menunjukkan cara memperbarui nilai dalam kolom jenis
Pointyang ditentukan pengguna , dengan mengonversi secara eksplisit dari string.UPDATE Cities SET Location = CONVERT(Point, '12.3:46.2') WHERE Name = 'Anchorage';Memanggil metode, ditandai sebagai mutator, dari jenis yang ditentukan pengguna, untuk melakukan pembaruan. Contoh berikut memanggil metode mutator jenis
PointbernamaSetXY. Ini memperbarui status instans jenis.UPDATE Cities SET Location.SetXY(23.5, 23.5) WHERE Name = 'Anchorage';Catatan
SQL Server mengembalikan kesalahan jika metode mutator dipanggil pada nilai null Transact-SQL, atau jika nilai baru yang dihasilkan oleh metode mutator null.
Memodifikasi nilai properti terdaftar atau anggota data publik dari jenis yang ditentukan pengguna. Ekspresi yang menyediakan nilai harus secara implisit dapat dikonversi ke jenis properti. Contoh berikut memodifikasi nilai properti
Xjenis yang ditentukanPointpengguna .UPDATE Cities SET Location.X = 23.5 WHERE Name = 'Anchorage';Untuk mengubah properti yang berbeda dari kolom jenis yang ditentukan pengguna yang sama, terbitkan beberapa pernyataan UPDATE, atau panggil metode mutator jenis.
Memperbarui data FILESTREAM
Anda dapat menggunakan pernyataan UPDATE untuk memperbarui bidang FILESTREAM ke nilai null, nilai kosong, atau jumlah data sebaris yang relatif kecil. Namun, sejumlah besar data lebih efisien dialirkan ke dalam file dengan menggunakan antarmuka Win32. Saat memperbarui bidang FILESTREAM, Anda memodifikasi data BLOB yang mendasar dalam sistem file. Ketika bidang FILESTREAM diatur ke NULL, data BLOB yang terkait dengan bidang dihapus. Anda tidak dapat menggunakan . WRITE(), untuk melakukan pembaruan parsial pada data FILESTREAM. Untuk informasi selengkapnya, lihat FILESTREAM (SQL Server).
Penanganan kesalahan
Jika pembaruan ke baris melanggar batasan atau aturan, melanggar pengaturan NULL untuk kolom, atau nilai baru adalah jenis data yang tidak kompatibel, pernyataan dibatalkan, kesalahan dikembalikan, dan tidak ada rekaman yang diperbarui.
Ketika pernyataan UPDATE mengalami kesalahan aritmatika (luapan, bagi dengan nol, atau kesalahan domain) selama evaluasi ekspresi, pembaruan tidak dilakukan. Sisa batch tidak dijalankan, dan pesan kesalahan dikembalikan.
Jika pembaruan ke kolom atau kolom yang berpartisipasi dalam indeks berkluster menyebabkan ukuran indeks berkluster dan baris melebihi 8.060 byte, pembaruan gagal dan pesan kesalahan dikembalikan.
Interoperabilitas
Pernyataan UPDATE diizinkan dalam isi fungsi yang ditentukan pengguna hanya jika tabel yang dimodifikasi adalah variabel tabel.
Saat pemicu INSTEAD OF didefinisikan pada tindakan UPDATE terhadap tabel, pemicu berjalan alih-alih pernyataan UPDATE. Versi SQL Server yang lebih lama hanya mendukung pemicu AFTER yang ditentukan pada UPDATE dan pernyataan modifikasi data lainnya. Klausa FROM tidak dapat ditentukan dalam pernyataan UPDATE yang mereferensikan, baik secara langsung atau tidak langsung, tampilan dengan pemicu yang INSTEAD OF ditentukan di atasnya. Untuk informasi selengkapnya tentang ALIH-ALIH pemicu, lihat MEMBUAT PEMICU (Transact-SQL).
Saat ini, klausul FROM tidak dapat ditentukan dalam pernyataan UPDATE pada Gudang di Microsoft Fabric. Pernyataan PEMBARUAN tabel tunggal didukung.
Pembatasan dan batasan
Klausa FROM tidak dapat ditentukan dalam pernyataan UPDATE yang mereferensikan, baik secara langsung atau tidak langsung, tampilan yang memiliki pemicu yang INSTEAD OF ditentukan di dalamnya. Untuk informasi selengkapnya tentang INSTEAD OF pemicu, lihat CREATE TRIGGER (Transact-SQL).
Ketika ekspresi tabel umum (CTE) adalah target pernyataan UPDATE, semua referensi ke CTE dalam pernyataan harus cocok. Misalnya, jika CTE diberi alias dalam klausul FROM, alias harus digunakan untuk semua referensi lain ke CTE. Referensi CTE yang tidak ambigu diperlukan karena CTE tidak memiliki ID objek, yang digunakan SQL Server untuk mengenali hubungan implisit antara objek dan aliasnya. Tanpa hubungan ini, rencana kueri dapat menghasilkan perilaku gabungan yang tidak terduga dan hasil kueri yang tidak diinginkan. Contoh berikut menunjukkan metode yang benar dan salah untuk menentukan CTE ketika CTE adalah objek target operasi pembaruan.
USE tempdb;
GO
-- UPDATE statement with CTE references that are correctly matched.
DECLARE @x TABLE (ID INT, Value INT);
DECLARE @y TABLE (ID INT, Value INT);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);
WITH cte AS (SELECT * FROM @x)
UPDATE x -- cte is referenced by the alias.
SET Value = y.Value
FROM cte AS x -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID;
SELECT * FROM @x;
GO
Berikut set hasilnya.
ID Value
------ -----
1 100
2 200
(2 row(s) affected)
Pernyataan UPDATE dengan referensi CTE yang salah dicocokkan.
USE tempdb;
GO
DECLARE @x TABLE (ID INT, Value INT);
DECLARE @y TABLE (ID INT, Value INT);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);
WITH cte AS (SELECT * FROM @x)
UPDATE cte -- cte is not referenced by the alias.
SET Value = y.Value
FROM cte AS x -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID;
SELECT * FROM @x;
GO
Berikut set hasilnya.
ID Value
------ -----
1 100
2 100
(2 row(s) affected)
Perilaku penguncian
Pernyataan UPDATE memperoleh kunci eksklusif (X) pada baris apa pun yang dimodifikasinya, dan menahan kunci ini sampai transaksi selesai. Bergantung pada rencana kueri untuk pernyataan UPDATE, jumlah baris yang dimodifikasi, dan tingkat isolasi transaksi, kunci dapat diperoleh di tingkat HALAMAN atau tingkat TABLE daripada tingkat ROW. Untuk menghindari kunci tingkat yang lebih tinggi ini, pertimbangkan untuk membagi pernyataan pembaruan yang memengaruhi ribuan baris atau lebih ke dalam batch, dan pastikan bahwa kondisi gabungan dan filter didukung oleh indeks. Lihat artikel tentang Mengunci di Mesin Database untuk detail selengkapnya tentang mekanika penguncian di SQL Server.
Perilaku pengelogan
Pernyataan UPDATE dicatat; namun, pembaruan parsial untuk jenis data nilai besar menggunakan . Klausa WRITE dicatat minimal. Untuk informasi selengkapnya, lihat "Memperbarui Tipe Data Nilai Besar" di bagian sebelumnya "Jenis Data".
Keamanan
Izin
UPDATE izin diperlukan pada tabel target. SELECT izin juga diperlukan untuk tabel yang diperbarui jika pernyataan UPDATE berisi klausa WHERE, atau jika ekspresi dalam klausa SET menggunakan kolom dalam tabel.
PERBARUI izin default untuk anggota sysadmin peran server tetap, db_owner peran database tetap dan db_datawriter , dan pemilik tabel. Anggota sysadminperan , db_owner, dan db_securityadmin , dan pemilik tabel dapat mentransfer izin ke pengguna lain.
Contoh
| Kategori | Elemen sintaksis unggulan |
|---|---|
| Sintaks Dasar | UPDATE |
| Membatasi Baris yang Diperbarui | WHERE * TOP * WITH common table expression * WHERE CURRENT OF |
| Mengatur Nilai Kolom | nilai komputasi * operator gabungan * nilai default * subkueri |
| Menentukan Objek Target Selain Tabel Standar | views * table variables * table aliass |
| Memperbarui Data Berdasarkan Data Dari Tabel Lain | DARI |
| Memperbarui Baris dalam Tabel Jarak Jauh | server tertaut * OPENQUERY * OPENDATASOURCE |
| Memperbarui Tipe Data Objek Besar | . WRITE * OPENROWSET |
| Memperbarui Jenis yang Ditentukan Pengguna | jenis yang ditentukan pengguna |
| Mengesampingkan Perilaku Default Pengoptimal Kueri dengan Menggunakan Petunjuk | petunjuk tabel * petunjuk kueri |
| Menangkap Hasil Pernyataan UPDATE | Klausa OUTPUT |
| Menggunakan UPDATE di Pernyataan Lain | Prosedur Tersimpan * TRY... MENANGKAP |
Sintaks dasar
Contoh di bagian ini menunjukkan fungsionalitas dasar pernyataan UPDATE menggunakan sintaks minimum yang diperlukan.
J. Menggunakan pernyataan UPDATE sederhana
Contoh berikut memperbarui satu kolom untuk semua baris dalam Person.Address tabel.
USE AdventureWorks2022;
GO
UPDATE Person.Address
SET ModifiedDate = GETDATE();
B. Memperbarui beberapa kolom
Contoh berikut memperbarui nilai dalam Bonuskolom , , CommissionPctdan SalesQuota untuk semua baris dalam SalesPerson tabel.
USE AdventureWorks2022;
GO
UPDATE Sales.SalesPerson
SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL;
GO
Membatasi Baris yang Diperbarui
Contoh di bagian ini menunjukkan cara yang dapat Anda gunakan untuk membatasi jumlah baris yang terpengaruh oleh pernyataan UPDATE.
C. Menggunakan klausa WHERE
Contoh berikut menggunakan klausa WHERE untuk menentukan baris mana yang akan diperbarui. Pernyataan memperbarui nilai dalam Color kolom Production.Product tabel untuk semua baris yang memiliki nilai 'Merah' yang ada di Color kolom dan memiliki nilai di Name kolom yang dimulai dengan 'Road-250'.
USE AdventureWorks2022;
GO
UPDATE Production.Product
SET Color = N'Metallic Red'
WHERE Name LIKE N'Road-250%' AND Color = N'Red';
GO
D. Menggunakan klausul TOP
Contoh berikut menggunakan klausul TOP untuk membatasi jumlah baris yang dimodifikasi dalam pernyataan UPDATE. Ketika klausul TOP (n) digunakan dengan UPDATE, operasi pembaruan dilakukan pada pilihan acak dari jumlah baris 'n'. Contoh berikut memperbarui VacationHours kolom sebesar 25 persen untuk 10 baris acak dalam Employee tabel.
USE AdventureWorks2022;
GO
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 ;
GO
Jika Anda harus menggunakan TOP untuk menerapkan pembaruan dalam kronologi yang bermakna, Anda harus menggunakan TOP bersama dengan ORDER BY dalam pernyataan subpilih. Contoh berikut memperbarui jam liburan 10 karyawan dengan tanggal sewa paling awal.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 BusinessEntityID FROM HumanResources.Employee
ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;
GO
E. Menggunakan klausa WITH common_table_expression
Contoh berikut memperbarui PerAssemblyQty nilai untuk semua bagian dan komponen yang digunakan secara langsung atau tidak langsung untuk membuat ProductAssemblyID 800. Ekspresi tabel umum mengembalikan daftar hierarkis bagian yang digunakan langsung untuk membangun ProductAssemblyID 800 dan bagian yang digunakan untuk membangun komponen tersebut, dan sebagainya. Hanya baris yang dikembalikan oleh ekspresi tabel umum yang dimodifikasi.
USE AdventureWorks2022;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
b.EndDate, 0 AS ComponentLevel
FROM Production.BillOfMaterials AS b
WHERE b.ProductAssemblyID = 800
AND b.EndDate IS NULL
UNION ALL
SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
bom.EndDate, ComponentLevel + 1
FROM Production.BillOfMaterials AS bom
INNER JOIN Parts AS p
ON bom.ProductAssemblyID = p.ComponentID
AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;
F. Menggunakan klausa WHERE CURRENT OF
Contoh berikut menggunakan klausa WHERE CURRENT OF untuk memperbarui hanya baris tempat kursor diposisikan. Saat kursor didasarkan pada gabungan, hanya table_name yang ditentukan dalam pernyataan UPDATE yang dimodifikasi. Tabel lain yang berpartisipasi dalam kursor tidak terpengaruh.
USE AdventureWorks2022;
GO
DECLARE complex_cursor CURSOR FOR
SELECT a.BusinessEntityID
FROM HumanResources.EmployeePayHistory AS a
WHERE RateChangeDate <>
(SELECT MAX(RateChangeDate)
FROM HumanResources.EmployeePayHistory AS b
WHERE a.BusinessEntityID = b.BusinessEntityID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO
Mengatur Nilai Kolom
Contoh di bagian ini menunjukkan pembaruan kolom dengan menggunakan nilai komputasi, subkueri, dan nilai DEFAULT.
G. Menentukan nilai komputasi
Contoh berikut menggunakan nilai komputasi dalam pernyataan UPDATE. Contoh menggandakan nilai dalam ListPrice kolom untuk semua baris dalam Product tabel.
USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2;
GO
H. Menentukan operator senyawa
Contoh berikut menggunakan variabel @NewPrice untuk menambah harga semua sepeda merah dengan mengambil harga saat ini dan menambahkan 10 ke dalamnya.
USE AdventureWorks2022;
GO
DECLARE @NewPrice INT = 10;
UPDATE Production.Product
SET ListPrice += @NewPrice
WHERE Color = N'Red';
GO
Contoh berikut menggunakan operator gabungan += untuk menambahkan data ' - tool malfunction' ke nilai yang ada di kolom Name untuk baris yang memiliki ScrapReasonID antara 10 dan 12.
USE AdventureWorks2022;
GO
UPDATE Production.ScrapReason
SET Name += ' - tool malfunction'
WHERE ScrapReasonID BETWEEN 10 and 12;
I. Menentukan subkueri dalam klausa SET
Contoh berikut menggunakan subkueri dalam klausa SET untuk menentukan nilai yang digunakan untuk memperbarui kolom. Subkueri hanya boleh mengembalikan nilai skalar (yaitu, satu nilai per baris). Contoh memodifikasi SalesYTD kolom dalam SalesPerson tabel untuk mencerminkan penjualan terbaru yang SalesOrderHeader dicatat dalam tabel. Subkueri mengagregasi penjualan untuk setiap tenaga penjualan dalam UPDATE pernyataan.
USE AdventureWorks2022;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD +
(SELECT SUM(so.SubTotal)
FROM Sales.SalesOrderHeader AS so
WHERE so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader AS so2
WHERE so2.SalesPersonID = so.SalesPersonID)
AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID
GROUP BY so.SalesPersonID);
GO
j. Memperbarui baris menggunakan nilai DEFAULT
Contoh berikut mengatur CostRate kolom ke nilai defaultnya (0,00) untuk semua baris yang memiliki CostRate nilai lebih besar dari 20.00.
USE AdventureWorks2022;
GO
UPDATE Production.Location
SET CostRate = DEFAULT
WHERE CostRate > 20.00;
Menentukan Objek Target Selain Tabel Standar
Contoh di bagian ini menunjukkan cara memperbarui baris dengan menentukan tampilan, alias tabel, atau variabel tabel.
K. Menentukan tampilan sebagai objek target
Contoh berikut memperbarui baris dalam tabel dengan menentukan tampilan sebagai objek target. Definisi tampilan mereferensikan beberapa tabel, namun, pernyataan UPDATE berhasil karena hanya mereferensikan kolom dari salah satu tabel yang mendasar. Pernyataan UPDATE akan gagal jika kolom dari kedua tabel ditentukan. Untuk informasi selengkapnya, lihat Mengubah Data Melalui Tampilan.
USE AdventureWorks2022;
GO
UPDATE Person.vStateProvinceCountryRegion
SET CountryRegionName = 'United States of America'
WHERE CountryRegionName = 'United States';
L. Menentukan alias tabel sebagai objek target
Contoh berikut memperbarui baris dalam tabel Production.ScrapReason. Alias tabel yang ditetapkan ke ScrapReason dalam klausul FROM ditentukan sebagai objek target dalam klausa UPDATE.
USE AdventureWorks2022;
GO
UPDATE sr
SET sr.Name += ' - tool malfunction'
FROM Production.ScrapReason AS sr
JOIN Production.WorkOrder AS wo
ON sr.ScrapReasonID = wo.ScrapReasonID
AND wo.ScrappedQty > 300;
M. Menentukan variabel tabel sebagai objek target
Contoh berikut memperbarui baris dalam variabel tabel.
USE AdventureWorks2022;
GO
-- Create the table variable.
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
NewVacationHours INT,
ModifiedDate DATETIME);
-- Populate the table variable with employee ID values from HumanResources.Employee.
INSERT INTO @MyTableVar (EmpID)
SELECT BusinessEntityID FROM HumanResources.Employee;
-- Update columns in the table variable.
UPDATE @MyTableVar
SET NewVacationHours = e.VacationHours + 20,
ModifiedDate = GETDATE()
FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = EmpID;
-- Display the results of the UPDATE statement.
SELECT EmpID, NewVacationHours, ModifiedDate FROM @MyTableVar
ORDER BY EmpID;
GO
Memperbarui Data Berdasarkan Data Dari Tabel Lain
Contoh di bagian ini menunjukkan metode pembaruan baris dari satu tabel berdasarkan informasi di tabel lain.
N. Menggunakan pernyataan UPDATE dengan informasi dari tabel lain
Contoh berikut memodifikasi SalesYTD kolom dalam SalesPerson tabel untuk mencerminkan penjualan terbaru yang SalesOrderHeader direkam dalam tabel.
USE AdventureWorks2022;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
ON sp.BusinessEntityID = so.SalesPersonID
AND so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = sp.BusinessEntityID);
GO
Contoh sebelumnya mengasumsikan bahwa hanya satu penjualan yang dicatat untuk tenaga penjual tertentu pada tanggal tertentu dan pembaruan tersebut saat ini. Jika lebih dari satu penjualan untuk tenaga penjualan tertentu dapat direkam pada hari yang sama, contoh yang ditampilkan tidak berfungsi dengan benar. Contoh berjalan tanpa kesalahan, tetapi setiap SalesYTD nilai diperbarui hanya dengan satu penjualan, terlepas dari berapa banyak penjualan yang benar-benar terjadi pada hari itu. Ini karena satu pernyataan UPDATE tidak pernah memperbarui baris yang sama dua kali.
Dalam situasi di mana lebih dari satu penjualan untuk tenaga penjualan tertentu dapat terjadi pada hari yang sama, semua penjualan untuk setiap orang penjualan harus dikumpulkan bersama-sama dalam UPDATE pernyataan, seperti yang ditunjukkan dalam contoh berikut:
USE AdventureWorks2022;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD +
(SELECT SUM(so.SubTotal)
FROM Sales.SalesOrderHeader AS so
WHERE so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader AS so2
WHERE so2.SalesPersonID = so.SalesPersonID)
AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID
GROUP BY so.SalesPersonID);
GO
Memperbarui baris dalam tabel jarak jauh
Contoh di bagian ini menunjukkan cara memperbarui baris dalam tabel target jarak jauh dengan menggunakan server tertaut atau fungsi set baris untuk mereferensikan tabel jarak jauh.
O. Memperbarui data dalam tabel jarak jauh dengan menggunakan server tertaut
Contoh berikut memperbarui tabel di server jarak jauh. Contoh dimulai dengan membuat tautan ke sumber data jarak jauh dengan menggunakan sp_addlinkedserver. Nama server tertaut, MyLinkedServer, kemudian ditentukan sebagai bagian dari nama objek empat bagian dalam form server.catalog.schema.object. Perhatikan bahwa Anda harus menentukan nama server yang valid untuk @datasrc.
USE master;
GO
-- Create a link to the remote data source.
-- Specify a valid server name for @datasrc as 'server_name' or 'server_nameinstance_name'.
EXEC sp_addlinkedserver @server = N'MyLinkedServer',
@srvproduct = N' ',
@provider = N'SQLNCLI10',
@datasrc = N'<server name>',
@catalog = N'AdventureWorks2022';
GO
USE AdventureWorks2022;
GO
-- Specify the remote data source using a four-part name
-- in the form linked_server.catalog.schema.object.
UPDATE MyLinkedServer.AdventureWorks2022.HumanResources.Department
SET GroupName = N'Public Relations'
WHERE DepartmentID = 4;
P. Memperbarui data dalam tabel jarak jauh dengan menggunakan fungsi OPENQUERY
Contoh berikut memperbarui baris dalam tabel jarak jauh dengan menentukan fungsi set baris OPENQUERY . Nama server tertaut yang dibuat dalam contoh sebelumnya digunakan dalam contoh ini.
UPDATE OPENQUERY (MyLinkedServer, 'SELECT GroupName FROM HumanResources.Department WHERE DepartmentID = 4')
SET GroupName = 'Sales and Marketing';
T. Memperbarui data dalam tabel jarak jauh dengan menggunakan fungsi OPENDATASOURCE
Contoh berikut memperbarui baris dalam tabel jarak jauh dengan menentukan fungsi set baris OPENDATASOURCE . Tentukan nama server yang valid untuk sumber data dengan menggunakan format server_name atau server_name\instance_name. Anda mungkin perlu mengonfigurasi instans SQL Server untuk Kueri Terdistribusi Ad Hoc. Untuk informasi selengkapnya, lihat Opsi Konfigurasi Server kueri terdistribusi ad hoc.
UPDATE OPENDATASOURCE('SQLNCLI', 'Data Source=<server name>;Integrated Security=SSPI').AdventureWorks2022.HumanResources.Department
SET GroupName = 'Sales and Marketing' WHERE DepartmentID = 4;
Memperbarui jenis data Objek Besar
Contoh di bagian ini menunjukkan metode pembaruan nilai dalam kolom yang ditentukan dengan jenis data objek besar (LOB).
R. Menggunakan UPDATE dengan . TULIS untuk mengubah data dalam kolom nvarchar(maks)
Contoh berikut menggunakan . Klausa WRITE untuk memperbarui nilai parsial dalam DocumentSummarykolom nvarchar (maks) dalam Production.Document tabel. Kata components diganti dengan kata features dengan menentukan kata pengganti, lokasi awal (offset) kata yang akan diganti dalam data yang ada, dan jumlah karakter yang akan diganti (panjang). Contoh ini juga menggunakan klausul OUTPUT untuk mengembalikan gambar DocumentSummary sebelum dan sesudah kolom ke @MyTableVar variabel tabel.
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
SummaryBefore NVARCHAR(max),
SummaryAfter NVARCHAR(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT deleted.DocumentSummary,
inserted.DocumentSummary
INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation';
SELECT SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO
S. Menggunakan UPDATE dengan . TULIS untuk menambahkan dan menghapus data dalam kolom nvarchar(maks)
Contoh berikut menambahkan dan menghapus data dari kolom nvarchar(maks) yang memiliki nilai yang saat ini diatur ke NULL. Karena . Klausa WRITE tidak dapat digunakan untuk mengubah kolom NULL, kolom pertama kali diisi dengan data sementara. Data ini kemudian diganti dengan data yang benar dengan menggunakan . Klausa WRITE. Contoh tambahan menambahkan data ke akhir nilai kolom, menghapus (memotong) data dari kolom dan, akhirnya, menghapus sebagian data dari kolom. Pernyataan SELECT menampilkan modifikasi data yang dihasilkan oleh setiap pernyataan UPDATE.
USE AdventureWorks2022;
GO
-- Replacing NULL value with temporary data.
UPDATE Production.Document
SET DocumentSummary = N'Replacing NULL value'
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
SELECT DocumentSummary
FROM Production.Document
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
-- Replacing temporary data with the correct data. Setting @Length to NULL
-- truncates all existing data from the @Offset position.
UPDATE Production.Document
SET DocumentSummary .WRITE(N'Carefully inspect and maintain the tires and crank arms.',0,NULL)
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
SELECT DocumentSummary
FROM Production.Document
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
-- Appending additional data to the end of the column by setting
-- @Offset to NULL.
UPDATE Production.Document
SET DocumentSummary .WRITE (N' Appending data to the end of the column.', NULL, 0)
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
SELECT DocumentSummary
FROM Production.Document
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
-- Removing all data from @Offset to the end of the existing value by
-- setting expression to NULL.
UPDATE Production.Document
SET DocumentSummary .WRITE (NULL, 56, 0)
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
SELECT DocumentSummary
FROM Production.Document
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
-- Removing partial data beginning at position 9 and ending at
-- position 21.
UPDATE Production.Document
SET DocumentSummary .WRITE ('',9, 12)
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
SELECT DocumentSummary
FROM Production.Document
WHERE Title = N'Crank Arm and Tire Maintenance';
GO
T. Menggunakan UPDATE dengan OPENROWSET untuk memodifikasi kolom varbinary(max)
Contoh berikut menggantikan gambar yang sudah ada yang disimpan dalam kolom varbinary(max) dengan gambar baru. Fungsi OPENROWSET digunakan dengan opsi MASSAL untuk memuat gambar ke dalam kolom. Contoh ini mengasumsikan bahwa file bernama Tires.jpg ada di jalur file yang ditentukan.
USE AdventureWorks2022;
GO
UPDATE Production.ProductPhoto
SET ThumbNailPhoto = (
SELECT *
FROM OPENROWSET(BULK 'c:Tires.jpg', SINGLE_BLOB) AS x )
WHERE ProductPhotoID = 1;
GO
U. Menggunakan UPDATE untuk mengubah data FILESTREAM
Contoh berikut menggunakan pernyataan UPDATE untuk mengubah data dalam file sistem file. Kami tidak merekomendasikan metode ini untuk mengalirkan data dalam jumlah besar ke file. Gunakan antarmuka Win32 yang sesuai. Contoh berikut menggantikan teks apa pun dalam rekaman file dengan teks Xray 1. Untuk informasi selengkapnya, lihat FILESTREAM (SQL Server).
UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' as VARBINARY(max))
WHERE [SerialNumber] = 2;
Memperbarui Jenis yang Ditentukan Pengguna
Contoh berikut mengubah nilai dalam kolom tipe yang ditentukan pengguna (UDT) CLR. Tiga metode ditunjukkan. Untuk informasi selengkapnya tentang kolom yang ditentukan pengguna, lihat Jenis yang Ditentukan Pengguna CLR.
V. Menggunakan jenis data sistem
Anda dapat memperbarui UDT dengan menyediakan nilai dalam jenis data sistem SQL Server, selama jenis yang ditentukan pengguna mendukung konversi implisit atau eksplisit dari jenis tersebut. Contoh berikut menunjukkan cara memperbarui nilai dalam kolom jenis Pointyang ditentukan pengguna , dengan mengonversi secara eksplisit dari string.
UPDATE dbo.Cities
SET Location = CONVERT(Point, '12.3:46.2')
WHERE Name = 'Anchorage';
W. Memanggil metode
Anda dapat memperbarui UDT dengan memanggil metode, yang ditandai sebagai mutator, dari jenis yang ditentukan pengguna, untuk melakukan pembaruan. Contoh berikut memanggil metode mutator jenis Point bernama SetXY. Ini memperbarui status instans jenis.
UPDATE dbo.Cities
SET Location.SetXY(23.5, 23.5)
WHERE Name = 'Anchorage';
X. Mengubah nilai properti atau anggota data
Anda dapat memperbarui UDT dengan memodifikasi nilai properti terdaftar atau anggota data publik dari jenis yang ditentukan pengguna. Ekspresi yang menyediakan nilai harus secara implisit dapat dikonversi ke jenis properti. Contoh berikut memodifikasi nilai properti X jenis yang ditentukan Pointpengguna .
UPDATE dbo.Cities
SET Location.X = 23.5
WHERE Name = 'Anchorage';
Mengesampingkan Perilaku Default Pengoptimal Kueri dengan Menggunakan Petunjuk
Contoh di bagian ini menunjukkan cara menggunakan petunjuk tabel dan kueri untuk mengesampingkan perilaku default pengoptimal kueri untuk sementara waktu saat memproses pernyataan UPDATE.
Perhatian
Karena pengoptimal kueri SQL Server biasanya memilih rencana eksekusi terbaik untuk kueri, kami menyarankan agar petunjuk hanya digunakan sebagai upaya terakhir oleh pengembang dan administrator database yang berpengalaman.
Y. Menentukan petunjuk tabel
Contoh berikut menentukan petunjuk tabel TABLOCK. Petunjuk ini menentukan bahwa kunci bersama diambil pada tabel Production.Product dan disimpan hingga akhir pernyataan UPDATE.
USE AdventureWorks2022;
GO
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO
Z. Menentukan petunjuk kueri
Contoh berikut menentukan petunjukOPTIMIZE FOR (@variable) kueri dalam pernyataan UPDATE. Petunjuk ini menginstruksikan pengoptimal kueri untuk menggunakan nilai tertentu untuk variabel lokal saat kueri dikompilasi dan dioptimalkan. Nilai hanya digunakan selama pengoptimalan kueri, dan bukan selama eksekusi kueri.
USE AdventureWorks2022;
GO
CREATE PROCEDURE Production.uspProductUpdate
@Product NVARCHAR(25)
AS
SET NOCOUNT ON;
UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE @Product
OPTION (OPTIMIZE FOR (@Product = 'BK-%') );
GO
-- Execute the stored procedure
EXEC Production.uspProductUpdate 'BK-%';
Menangkap hasil pernyataan UPDATE
Contoh di bagian ini menunjukkan cara menggunakan Klausul OUTPUT untuk mengembalikan informasi dari, atau ekspresi berdasarkan, setiap baris yang terpengaruh oleh pernyataan UPDATE. Hasil ini dapat dikembalikan ke aplikasi pemrosesan untuk digunakan dalam hal-hal seperti pesan konfirmasi, pengarsipan, dan persyaratan aplikasi lainnya.
AA. Menggunakan UPDATE dengan klausa OUTPUT
Contoh berikut memperbarui kolom VacationHours dalam Employee tabel sebesar 25 persen untuk Karyawan dengan kurang dari 10 VacationHours dan juga mengatur nilai dalam kolom ModifiedDate ke tanggal saat ini. Klausa OUTPUT mengembalikan nilai VacationHours yang ada sebelum menerapkan UPDATE pernyataan di deleted.VacationHours kolom dan nilai yang inserted.VacationHours diperbarui dalam kolom ke @MyTableVar variabel tabel.
Dua SELECT pernyataan mengikuti yang mengembalikan nilai di @MyTableVar dan hasil operasi pembaruan dalam Employee tabel. Untuk contoh selengkapnya menggunakan klausul OUTPUT, lihat Klausul OUTPUT (Transact-SQL).
USE AdventureWorks2022;
GO
--Display the initial data of the table to be updated.
SELECT BusinessEntityID, VacationHours, ModifiedDate, HireDate
FROM HumanResources.Employee
WHERE VacationHours < 10
GO
DECLARE @MyTableVar TABLE (
EmpID int NOT NULL,
OldVacationHours smallint,
NewVacationHours smallint,
ModifiedDate datetime);
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT inserted.BusinessEntityID,
deleted.VacationHours,
inserted.VacationHours,
inserted.ModifiedDate
INTO @MyTableVar
WHERE VacationHours < 10
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours
, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT BusinessEntityID, VacationHours, ModifiedDate, HireDate
FROM HumanResources.Employee
WHERE VacationHours < 10
GO
Menggunakan UPDATE dalam pernyataan lain
Contoh di bagian ini menunjukkan cara menggunakan UPDATE dalam pernyataan lain.
AB. Menggunakan UPDATE dalam prosedur tersimpan
Contoh berikut menggunakan pernyataan UPDATE dalam prosedur tersimpan. Prosedur ini mengambil satu parameter input, @NewHours dan satu parameter @RowCountoutput . Nilai @NewHours parameter digunakan dalam pernyataan UPDATE untuk memperbarui kolom VacationHours dalam tabel HumanResources.Employee. Parameter @RowCount output digunakan untuk mengembalikan jumlah baris yang terpengaruh ke variabel lokal. Ekspresi CASE digunakan dalam klausul SET untuk menentukan nilai yang diatur secara kondisional untuk VacationHours. Ketika karyawan dibayar per jam (SalariedFlag = 0), VacationHours diatur ke jumlah jam saat ini ditambah nilai yang ditentukan dalam @NewHours; jika tidak, VacationHours diatur ke nilai yang ditentukan dalam @NewHours.
USE AdventureWorks2022;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours SMALLINT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
ELSE @NewHours
END
)
WHERE CurrentFlag = 1;
GO
EXEC HumanResources.Update_VacationHours 40;
AC. Menggunakan UPDATE dalam TRY... Blok CATCH
Contoh berikut menggunakan pernyataan UPDATE dalam TRY... Blok CATCH untuk menangani kesalahan eksekusi yang mungkin terjadi selama operasi pembaruan.
USE AdventureWorks2022;
GO
BEGIN TRANSACTION;
BEGIN TRY
-- Intentionally generate a constraint violation error.
UPDATE HumanResources.Department
SET Name = N'MyNewName'
WHERE DepartmentID BETWEEN 1 AND 2;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)
AD. Menggunakan pernyataan UPDATE sederhana
Contoh berikut menunjukkan bagaimana semua baris dapat terpengaruh ketika klausa WHERE tidak digunakan untuk menentukan baris (atau baris) yang akan diperbarui.
Contoh ini memperbarui nilai dalam EndDate kolom dan CurrentFlag untuk semua baris dalam DimEmployee tabel.
-- Uses AdventureWorks
UPDATE DimEmployee
SET EndDate = '2010-12-31', CurrentFlag='False';
Anda juga dapat menggunakan nilai komputasi dalam pernyataan UPDATE. Contoh berikut menggandakan ListPrice nilai dalam kolom untuk semua baris dalam Product tabel.
-- Uses AdventureWorks
UPDATE DimEmployee
SET BaseRate = BaseRate * 2;
Æ. Menggunakan pernyataan UPDATE dengan klausa WHERE
Contoh berikut menggunakan klausa WHERE untuk menentukan baris mana yang akan diperbarui.
-- Uses AdventureWorks
UPDATE DimEmployee
SET FirstName = 'Gail'
WHERE EmployeeKey = 500;
AF. Menggunakan pernyataan UPDATE dengan label
Contoh berikut menunjukkan penggunaan LABEL untuk pernyataan UPDATE.
-- Uses AdventureWorks
UPDATE DimProduct
SET ProductSubcategoryKey = 2
WHERE ProductKey = 313
OPTION (LABEL = N'label1');
AG. Menggunakan pernyataan UPDATE dengan informasi dari tabel lain
Contoh ini membuat tabel untuk menyimpan total penjualan menurut tahun. Ini memperbarui total penjualan untuk tahun 2004 dengan menjalankan pernyataan SELECT terhadap tabel FactInternetSales.
-- Uses AdventureWorks
CREATE TABLE YearlyTotalSales (
YearlySalesAmount MONEY NOT NULL,
Year SMALLINT NOT NULL )
WITH ( DISTRIBUTION = REPLICATE );
INSERT INTO YearlyTotalSales VALUES (0, 2004);
INSERT INTO YearlyTotalSales VALUES (0, 2005);
INSERT INTO YearlyTotalSales VALUES (0, 2006);
UPDATE YearlyTotalSales
SET YearlySalesAmount=
(SELECT SUM(SalesAmount) FROM FactInternetSales WHERE OrderDateKey >=20040000 AND OrderDateKey < 20050000)
WHERE Year=2004;
SELECT * FROM YearlyTotalSales;
AH. Gabungan ANSI untuk pernyataan pembaruan
Contoh ini memperlihatkan cara memperbarui data berdasarkan hasil dari menggabungkan tabel lain.
CREATE TABLE dbo.Table1
(ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2
(ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0);
INSERT INTO dbo.Table2 VALUES(1, 0.0);
GO
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO
SELECT ColA, ColB
FROM dbo.Table2;
GO
Lihat Juga
BUAT TABEL (Transact-SQL)
BUAT PEMICU (Transact-SQL)
Kursor (Transact-SQL)
DELETE (Transact-SQL)
INSERT (Transact-SQL)
Fungsi Teks dan Gambar (Transact-SQL)
DENGAN common_table_expression (T-SQL)
FILESTREAM (SQL Server)
Dukungan Kolajeasi dan Unicode
Kumpulan Karakter Byte Tunggal dan Multibyte