Php mysqli memperbarui beberapa baris

Untuk ini, Anda dapat menggunakan MySQL IN(). Pertama-tama mari kita buat −

mysql> create table DemoTable1420
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> FirstName varchar(20),
   -> LastName varchar(20),
   -> Age int
   -> );
Query OK, 0 rows affected (1.12 sec)

Masukkan beberapa record ke dalam tabel menggunakan insert −

mysql> insert into DemoTable1420(FirstName,LastName,Age) values('Chris','Brown',23);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1420(FirstName,LastName,Age) values('David','Miller',22);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable1420(FirstName,LastName,Age) values('John','Smith',24);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1420(FirstName,LastName,Age) values('John','Doe',21);
Query OK, 1 row affected (0.43 sec)
mysql> insert into DemoTable1420(FirstName,LastName,Age) values('Adam','Smith',25);
Query OK, 1 row affected (0.18 sec)

Tampilkan semua rekaman dari tabel menggunakan pilih −

mysql> select * from DemoTable1420;

Ini akan menghasilkan output berikut -

+----+-----------+----------+------+
| Id | FirstName | LastName | Age  |
+----+-----------+----------+------+
|  1 | Chris     | Brown    |   23 |
|  2 | David     | Miller   |   22 |
|  3 | John      | Smith    |   24 |
|  4 | John      | Doe      |   21 |
|  5 | Adam      | Smith    |   25 |
+----+-----------+----------+------+
5 rows in set (0.00 sec)

Berikut adalah kueri untuk memperbarui banyak baris menggunakan single where clause −

mysql> update DemoTable1420
   -> set FirstName='Carol',LastName='Taylor'
   -> where Id IN(1,3,4,5);
Query OK, 4 rows affected (0.42 sec)
Rows matched: 4  Changed: 4 Warnings: 0

Mari kita periksa catatan tabel sekali lagi −

mysql> select * from DemoTable1420;

Ini akan menghasilkan output berikut -

+----+-----------+----------+------+
| Id | FirstName | LastName | Age  |
+----+-----------+----------+------+
|  1 | Carol     | Taylor   | 23   |
|  2 | David     | Miller   | 22   |
|  3 | Carol     | Taylor   | 24   |
|  4 | Carol     | Taylor   | 21   |
|  5 | Carol     | Taylor   | 25   |
+----+-----------+----------+------+
5 rows in set (0.00 sec)

Dalam latihan ini, kita akan belajar memperbarui beberapa baris dengan nilai yang berbeda dalam satu kueri. Misalkan kita memiliki catatan karyawan berikut dan kami ingin memperbarui nomor telepon beberapa karyawan -

CREATE TABLE IF NOT EXISTS `empdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(25) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `empdata` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Anjali', This email address is being protected from spambots. You need JavaScript enabled to view it.', 878433948),
(2, 'Priska', This email address is being protected from spambots. You need JavaScript enabled to view it.', 493905490),
(3, 'Abhi', This email address is being protected from spambots. You need JavaScript enabled to view it.', 403022139),
(4, 'Joya', This email address is being protected from spambots. You need JavaScript enabled to view it.', 342345329),
(5, 'Ammy', This email address is being protected from spambots. You need JavaScript enabled to view it.', 239848342),
(6, 'Lussi', This email address is being protected from spambots. You need JavaScript enabled to view it.', 490290331);

Cara terbaik untuk memperbarui banyak baris hanya dalam satu pernyataan adalah menggunakan pernyataan CASE WHEN ELSE. Dalam hal ini, pernyataan akan memperbarui kasus yang cocok dan berakhir sebaliknya, seperti-

UPDATE 'table_name' 
SET 'field_name' = CASE 'id'
WHEN '1' THEN 'value 1'
WHEN '2' THEN 'value 2'
WHEN '3' THEN 'value 3'
ELSE 'field_name'
END

Di sini, 'table_name' adalah nama tabel MySQL, 'field_name' adalah nama kolom

Permintaan pembaruan untuk memperbarui nomor telepon beberapa karyawan adalah sebagai berikut -

UPDATE empdata SET phone = CASE 
WHEN id=1 THEN '840932345'
WHEN id=2 THEN '890040908'
WHEN id=3 THEN '675748389'
WHEN id=6 THEN '909030309'
ELSE phone
END;

mari kita periksa data yang diperbarui -

SELECT * FROM 'empdata';

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  6 | Lussi  | This email address is being protected from spambots. You need JavaScript enabled to view it.  | 909030309 |
|  4 | Joya   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 342345329 |
|  5 | Ammy   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 239848342 |
|  2 | Priska | This email address is being protected from spambots. You need JavaScript enabled to view it. | 890040908 |
|  3 | Abhi   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 675748389 |
|  1 | Anjali | This email address is being protected from spambots. You need JavaScript enabled to view it. | 840932345 |
+----+--------+--------------------+-----------+

Latihan MySQL Terkait

Bagaimana cara memperbarui banyak baris sekaligus di PHP?

Bagaimana cara memperbarui beberapa baris sekaligus?

Bisakah kita memperbarui banyak baris dalam satu pernyataan pembaruan?

Bagaimana cara memperbarui beberapa baris kolom tunggal di MySQL?