Php mysql memperbarui beberapa baris sekaligus

Pada artikel ini, kita akan mempelajari penggunaan pernyataan CASE, fungsi IF(), klausa UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 0 dan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 1 dengan fungsi UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 2 untuk memperbarui beberapa kolom dalam beberapa baris dengan nilai yang berbeda di MySQL

Perbarui Banyak Kolom dalam Banyak Catatan (Baris) Dengan Nilai Berbeda di MySQL

Terkadang, kita perlu memperbarui beberapa kolom dalam beberapa baris dengan nilai yang berbeda di database. Tidak apa-apa untuk menggunakan beberapa pernyataan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); _1 jika kita memiliki beberapa catatan dalam tabel

Misalkan ada jutaan baris dalam tabel. Beberapa cara untuk memperbarui tabel tercantum di bawah ini

1. Gunakan pernyataan CASE
2. Gunakan fungsi IF()
3. Gunakan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 0
4. Gunakan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 1 dengan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 2

Untuk mempelajari pendekatan yang disebutkan di atas, buat tabel bernama UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 9 memiliki UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 0, UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 1, dan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 2 sebagai atribut (kolom) di mana UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 0 adalah primary key. Anda dapat mengikuti tutorial ini dengan menggunakan kueri di bawah untuk membuat dan mengisi tabel

Kode Contoh

# create a table CREATE TABLE students( ID INT NOT NULL, JavaScore INT NOT NULL, PythonScore INT NOT NULL, PRIMARY KEY (ID)); # insert data INSERT INTO students (ID, JavaScore, PythonScore) VALUES (1, 70, 65), (2, 75, 80), (3, 81, 89), (4, 50, 70); # display table data SELECT * FROM students;

Keluaran

IDJavaScorePythonScore17065275803818945070

Setelah tabel UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); _9 dibuat dan diisi, kita dapat menggunakan pendekatan yang disebutkan

Gunakan Pernyataan CASE

Kode Contoh

UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4);

Gunakan pernyataan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); _6 untuk mendapatkan hasil yang diperbarui

Keluaran

IDJavaScorePythonScore17570280853869445575

Kami memperbarui beberapa kolom pada beberapa baris dengan nilai berbeda menggunakan pernyataan CASE yang melewati semua kondisi dan menampilkan item (nilai) saat kondisi pertama terpenuhi (seperti pernyataan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 8). Itu berhenti membaca setelah kondisinya UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); _9 dan mengembalikan hasil yang sesuai

Misalkan tidak ada kondisi UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); _9, maka bagian UPDATE students SET JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))), PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL)))) WHERE ID IN (1,2,3,4); 1 dijalankan. Dengan tidak adanya bagian UPDATE students SET JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))), PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL)))) WHERE ID IN (1,2,3,4); _1, ia mengembalikan UPDATE students SET JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))), PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL)))) WHERE ID IN (1,2,3,4); 3

Jika ada bidang lain dari jenis UPDATE students SET JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))), PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL)))) WHERE ID IN (1,2,3,4); _4 yang ingin kita pertahankan konstan untuk semua rekaman, kuerinya adalah sebagai berikut

Kode Contoh

UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); _

Gunakan Fungsi IF()

Kode Contoh

UPDATE students SET JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))), PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL)))) WHERE ID IN (1,2,3,4);

Jalankan perintah UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 6 untuk mendapatkan nilai baru dari tabel UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 9

Keluaran

IDJavaScorePythonScore17671281863879545676

Kami menggunakan fungsi IF()_ yang mengembalikan nilai tertentu jika kondisinya terpenuhi. Jika tidak, ia mengembalikan nilai lain yang ditentukan. Sintaksnya adalah UPDATE students SET JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))), PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL)))) WHERE ID IN (1,2,3,4); _9

Anda mungkin memiliki pertanyaan jika kondisi IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, FalseValue) ) ) ) _0 terpenuhi, lalu mengapa harus ke IF() lainnya?

IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, FalseValue) ) ) )

Mari kita membuatnya lebih sederhana untuk dipahami. Dalam cuplikan berikut, kami memiliki beberapa IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, FalseValue) ) ) ) 3, dan tidak masalah apakah kondisi terpenuhi atau tidak

Setiap kondisi IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, FalseValue) ) ) ) _5 akan diperiksa dan ditetapkan nilainya sesuai. IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, FalseValue) ) ) ) _5 terakhir memiliki UPDATE students SET JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))), PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL)))) WHERE ID IN (1,2,3,4); 1 bagian, yang hanya akan berjalan jika kondisi IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, FalseValue) ) ) ) 5 keempat adalah IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, IF(condition, TrueValue, FalseValue) ) ) ) 9

IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue ELSE FalseValue

Alasan menggunakan fungsi IF() bersarang adalah untuk memperbarui beberapa baris dengan nilai yang berbeda

Saat diminta untuk memperbarui beberapa kolom dalam beberapa baris, kami lebih suka menggunakan pernyataan CASE karena lebih mudah dipahami dan dikelola daripada fungsi IF() bersarang

Kode Contoh

INSERT INTO students (ID, JavaScore, PythonScore) VALUES (1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77) ON DUPLICATE KEY UPDATE JavaScore = VALUES(JavaScore), PythonScore = VALUES(PythonScore);

Keluaran

IDJavaScorePythonScore17772282873889645777

Contoh ini menunjukkan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 0. Biasanya, ketika kita IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue ELSE FalseValue _4 ke dalam tabel tertentu yang dapat menyebabkan duplikat dalam indeks IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue ELSE FalseValue 5 atau IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue ELSE FalseValue 6, hal itu menyebabkan kesalahan

Namun, MySQL memperbarui catatan yang ada dengan nilai terbaru jika kami menentukan IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue ELSE FalseValue 7. Jika duplikat di IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue ELSE FalseValue 5 ditemukan, nilai untuk kolom tersebut akan ditetapkan ke nilai saat ini

Meskipun fungsi IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue ELSE FalseValue _9 berfungsi saat menulis tutorial ini, ini menunjukkan peringatan bahwa fungsi IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue IF Condition TrueValue ELSE FalseValue 9 tidak digunakan lagi dan akan dihapus di rilis mendatang. Anda dapat mempertimbangkan Dokumentasi MySQL untuk bantuan lebih lanjut

Kode Contoh

UPDATE students std JOIN ( SELECT 1 AS ID, 78 AS JavaScore, 73 AS PythonScore UNION ALL SELECT 2 AS ID, 83 AS JavaScore, 88 AS PythonScore UNION ALL SELECT 3 AS ID, 89 AS JavaScore, 97 AS PythonScore UNION ALL SELECT 4 AS ID, 58 AS JavaScore, 78 AS PythonScore ) temp ON std.ID = temp.ID SET std.JavaScore = temp.JavaScore, std.PythonScore = temp.PythonScore;

Solusi ini hanya akan berfungsi jika INSERT INTO students (ID, JavaScore, PythonScore) VALUES (1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77) ON DUPLICATE KEY UPDATE JavaScore = VALUES(JavaScore), PythonScore = VALUES(PythonScore); 1 dinonaktifkan. Kita dapat menonaktifkannya di MySQL Workbench dengan membuka INSERT INTO students (ID, JavaScore, PythonScore) VALUES (1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77) ON DUPLICATE KEY UPDATE JavaScore = VALUES(JavaScore), PythonScore = VALUES(PythonScore); 2 dan hapus centang pada opsi INSERT INTO students (ID, JavaScore, PythonScore) VALUES (1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77) ON DUPLICATE KEY UPDATE JavaScore = VALUES(JavaScore), PythonScore = VALUES(PythonScore); 3

Kemudian, restart server MySQL, jalankan query yang diberikan di atas dan gunakan perintah INSERT INTO students (ID, JavaScore, PythonScore) VALUES (1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77) ON DUPLICATE KEY UPDATE JavaScore = VALUES(JavaScore), PythonScore = VALUES(PythonScore); 4 untuk mendapatkan hasil berikut

Keluaran

IDJavaScorePythonScore17873283883899745878

Kami mengumpulkan data di dalam UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); _2 menggunakan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 6 dan INSERT INTO students (ID, JavaScore, PythonScore) VALUES (1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77) ON DUPLICATE KEY UPDATE JavaScore = VALUES(JavaScore), PythonScore = VALUES(PythonScore); 7. Setelah selesai, kami menggabungkan semua data menggunakan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end) WHERE ID in (1,2,3,4); 2 dan menyetel UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 1 dan UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 2 pada setiap kondisi yang memuaskan untuk atribut UPDATE students SET JavaScore = (case when ID = 1 then 75 when ID = 2 then 80 when ID = 3 then 86 when ID = 4 then 55 end), PythonScore = (case when ID = 1 then 70 when ID = 2 then 85 when ID = 3 then 94 when ID = 4 then 75 end), DATEANDTIME = NOW() WHERE ID in (1,2,3,4); 0

Bagaimana cara MEMPERBARUI beberapa baris sekaligus di MySQL?

Bisakah kita MEMPERBARUI beberapa baris dalam satu pernyataan UPDATE?

Bagaimana cara MEMPERBARUI beberapa kolom dalam SQL menggunakan PHP?

Bagaimana cara saya MEMPERBARUI beberapa baris dalam satu kolom di SQL?