Menjelajahi Subkueri dalam Pernyataan INSERT, UPDATE, DELETE

Disertai dengan beberapa contoh di basis data pub

Daftar Isi

· Pendahuluan
· Basis Data pub
· Subkueri dalam Pernyataan INSERT
∘ Sintaks Dasar
∘ Contoh
· Subkueri dalam Pernyataan UPDATE
∘ Sintaks Dasar
∘ Contoh
· Subkueri dalam Pernyataan DELETE
∘ Sintaks Dasar
∘ Contoh
· Penutup
· Referensi

Pendahuluan

Structured Query Language (SQL) adalah alat yang ampuh untuk mengelola dan memanipulasi basis data relasional. Salah satu fitur yang meningkatkan fleksibilitas dan kemampuan SQL adalah penggunaan subkueri. Subkueri adalah kueri yang tertanam dalam pernyataan SQL lain. Subkueri ini dapat digunakan dengan berbagai pernyataan SQL, termasuk SELECT, INSERT, UPDATE, dan DELETE, menawarkan cara serbaguna untuk mengambil, memodifikasi, atau menghapus data berdasarkan hasil kueri lain.

Pada artikel sebelumnya, Subkueri dalam Klausul SELECT, FROM, WHERE, dan HAVING, kita telah membahas subkueri untuk pengambilan data dalam pernyataan SELECT. Di bagian selanjutnya dari artikel ini, kita akan mengeksplorasi integrasi subkueri dalam berbagai pernyataan SQL untuk mengubah data seperti INSERT, UPDATE, DELETE, dan disertai dengan beberapa contoh.

Basis Data pub

Artikel ini menggunakan basis data pub. Anda dapat mengunduh skripnya, instpubs.sql , dari tautan di bawah dan eksekusi di SQL Server Management Studio.

sql-server-samples/samples/databases/northwind-pubs at master · microsoft/sql-server-samples

Berikut diagramnya.

Subkueri dalam Pernyataan INSERT

Subkueri dalam pernyataan INSERT menyediakan cara untuk memasukkan data ke dalam tabel berdasarkan hasil kueri lain. Hal ini sangat berguna ketika Anda ingin mengisi tabel dengan data dari tabel yang sudah ada atau ketika data yang akan dimasukkan bergantung pada hasil subkueri. Mari kita jelajahi lebih detail.

Sintaks Dasar

Sintaks dasar untuk menggunakan subkueri dalam pernyataan INSERT adalah sebagai berikut.

INSERT  INTO table_target (kolom1, kolom2, ...) 
VALUES (subkueri);

• tabel_target: tabel tempat Anda ingin memasukkan data.
• (kolom1, kolom2, ...): kolom tertentu di tabel target tempat data akan dimasukkan.
• (subkueri): subkueri yang mengambil data yang akan dimasukkan.

Contoh

Subkueri baris tunggal

USE pubs
GO
INSERT INTO jobs
       (job_id
       ,job_desc
       ,min_lvl
       ,max_lvl)
VALUES ((SELECT MAX(job_id) + 1
         FROM jobs)
       ,'Analyzer'
       , 50
       , 125);

Contoh ini menyisipkan pekerjaan baru dengan ID yang lebih besar dari ID pekerjaan maksimum saat ini.

Jika Anda mendapatkan pesan error seperti di bawah ini:

Msg 544, Level 16, State 1, Line 12
Cannot insert explicit value for identity column in table 'jobs' when IDENTITY_INSERT is set to OFF.

Subkueri multi-baris

Dalam beberapa kasus, Anda mungkin menggunakan subkueri secara langsung dalam pernyataan SELECT pada INSERT.

USE pubs
-- Memasukkan data langsung dari subquery
INSERT INTO employee_1994
SELECT *
FROM employee
WHERE Year(hire_date) = 1994

Contoh ini menyisipkan karyawan dengan tanggal perekrutan 1994 ke dalam tabel Employee_1994.

Anda akan mendapatkan pesan kesalahan berikut jika Anda menjalankannya.

Msg 208, Level 16, State 1, Line 3
Invalid object name 'employee_1994'.

Anda harus membuat tabel employee_1994 terlebih dahulu dengan skema yang sama dengan employee.

-- Buat tabel Employee_1994 
SELECT *
INTO employee_1994
FROM employee
WHERE 2 = 0
GO
-- Langsung memasukkan data dari subquery 
INSERT INTO employee_1994
SELECT *
FROM employee
WHERE Year(hire_date) = 1994

Anda dapat melakukan hal yang sama tanpa harus menggunakan subkueri.

SELECT *
INTO employee_1994
FROM employee
WHERE Year(hire_date) = 1994

Sekarang Anda memiliki data di employee_1994.

Menggunakan subkueri dalam pernyataan INSERT meningkatkan fleksibilitas penyisipan data, memungkinkan Anda mendasarkan data baru pada hasil kueri lainnya.

Subkueri dalam Pernyataan UPDATE

Subkueri dalam pernyataan UPDATE memungkinkan Anda mengubah data dalam tabel berdasarkan hasil kueri lain. Ini bisa berguna ketika Anda ingin memperbarui rekaman menggunakan informasi dari tabel yang sama atau tabel lain. Mari jelajahi subkueri dalam pernyataan UPDATE secara lebih detail.

Sintaks Dasar

Sintaks dasar untuk menggunakan subkueri dalam pernyataan UPDATE adalah sebagai berikut.

UPDATE table_target 
SET kolomx = (subkueri) 
WHERE kondisi;

• target_table: tabel yang ingin Anda perbarui.
• kolomx = (subkueri): menentukan kolom yang akan diperbarui dan subkueri yang memberikan nilai baru.
• kondisi: menetapkan kondisi yang harus dipenuhi agar pembaruan dapat dilakukan.

Contoh

Subkueri skalar

-- Memperbarui satu kolom berdasarkan subkueri 
UPDATE jobs
SET max_lvl = (SELECT 0.5*AVG(max_lvl)
               FROM jobs
               WHERE job_id <> 1)
WHERE job_id = 1;

Dalam contoh ini, max_lvl pada tabel jobs untuk job_id = 1diperbarui menjadi 0.5*AVG(min_lvl) di mana job_id <> 1.

Berikut contoh lain.

-- Memperbarui beberapa kolom berdasarkan subkueri
UPDATE jobs
SET min_lvl = (SELECT MIN(min_lvl)
               FROM jobs
               WHERE job_id <> 1),
    max_lvl = (SELECT 0.5*AVG(max_lvl)
               FROM jobs
               WHERE job_id <> 1)
WHERE job_id NOT IN 
              (SELECT job_id
               FROM employee);

Untuk job_id yang tidak ada dalam tabel karyawan, nilai min_lvl diperbarui ke MIN(min_lvl)dan max_lvldiperbarui ke 0.5*AVG(max_lvl) di mana job_id <> 1.

Untuk job_id yang tidak ada dalam tabel karyawan, nilai min_lvl dan max_lvl dalam tabel jobs berturut-turut diperbarui ke MIN(min_lvl) dan 0.5*AVG(max_lvl).

Subkueri dalam pernyataan UPDATE menyediakan mekanisme yang kuat untuk memperbarui data secara dinamis berdasarkan hasil kueri lainnya. Sangat penting untuk memastikan bahwa subkueri mengembalikan tipe data dan hasil yang diharapkan untuk menghindari potensi kesalahan selama eksekusi.

Subkueri dalam Pernyataan DELETE

Subkueri dalam pernyataan DELETE memungkinkan Anda menghapus rekaman dari tabel berdasarkan hasil kueri lain. Ini berguna ketika Anda ingin menghapus baris dari tabel secara selektif berdasarkan kondisi atau kriteria tertentu.

Sintaks Dasar

Sintaks dasar untuk menggunakan subkueri dalam pernyataan DELETE adalah sebagai berikut.

DELETE FROM table_target
WHERE kondisi
AND kolom OPERATOR (subkueri);

• table_target: tabel yang akan dihapus.
• kondisi: menentukan kondisi yang harus dipenuhi agar penghapusan dapat terjadi.
• kolom OPERATOR (subkueri): subkueri yang mengidentifikasi baris yang akan dihapus berdasarkan kondisi yang ditentukan.

Contoh

Hapus penulis yang tidak menulis publikasi apa pun.

USE pubs
GO
DELETE FROM authors
WHERE au_id NOT IN
     (SELECT au_id
      FROM titleauthor)

Hapus pekerjaan yang tidak ada karyawannya.

HAPUS  DARI pekerjaan 
DI MANA job_id TIDAK  MASUK
      ( PILIH job_id 
      DARI karyawan)

Subkueri dalam pernyataan DELETE menyediakan cara yang fleksibel untuk menghapus baris berdasarkan berbagai kondisi. Penting untuk membuat subkueri dengan hati-hati untuk memastikan bahwa subkueri tersebut mengidentifikasi kumpulan rekaman yang benar untuk dihapus. Selalu uji pernyataan DELETE Anda dengan pernyataan SELECT terlebih dahulu untuk memverifikasi baris yang akan terpengaruh.

Penutup

Subkueri dalam pernyataan INSERT, UPDATE, dan DELETE menawarkan cara yang fleksibel untuk memanipulasi data berdasarkan hasil kueri lainnya. Baik Anda menyisipkan, memperbarui, atau menghapus baris, subkueri menyediakan mekanisme yang kuat untuk operasi data dinamis dan bersyarat dalam SQL.

Referensi

Database Principles

Subkueri (SQL Server) - SQL Server