CODEWITHSUNDEEP
mysqldatabase
Blake
Blake

Reputation: 33

MySQL update multiple rows based on id

In a table in MySQL I'm trying to update a specific field based on the id.

The table has more than 5,000 rows and many fields. One of the fields is "id" and another one that I want to update is called "category" which right now all of them are NULL and I want to update all of them.

my backup mysql file that I want to use has only "id" and "category" which they are like this:

INSERT INTO `products` (`id`, `category`) VALUES
(3, 1),
(4, 1),
(5, 2),
(6, 1),
(7, 5),
(8, 1),
(9, 6),
(10, 1),
...
(5000, 3);

I want to update the "category" field in my table according to the id's in this list and because there is more than 5,000 rows I don't want to change each record manually.

Right now in my table all the "category" fields are NULL and I want to update or give new information to the "category" fields using the file that I have.

Upvotes: 2

Views: 3138

Answers (2)

user10923223
user10923223

Reputation:

Perhaps the better solution for you can be:

First of all, create temporary table 

 CREATE TABLE `products_tmp` (
    `id` INT NOT NULL,
    `category` INT NOT NULL,
    PRIMARY KEY (`id`)
 );

After that, perform inserts into generated temporary table:

INSERT INTO `products_tmp` (`id`, `category`) VALUES
(3, 1),
(4, 1),
(5, 2),
(6, 1),
(7, 5),
(8, 1),
(9, 6),
(10, 1),
...
(5000, 3);

after this, you can update all fields in your original table:

UPDATE products p
JOIN products_tmp pt ON p.id = pt.id
SET p.category = pt.category;

After this, you can delete temporary table

drop table products_tmp;

Upvotes: 0

Daniel E.
Daniel E.

Reputation: 2480

The easiest is to use a Temporary table : 

CREATE TEMPORARY TABLE temp_products (id int, category int ) ;

Then 

INSERT INTO `temp_products` (`id`, `category`) VALUES
(3, 1),
(4, 1),
(5, 2),
(6, 1),
(7, 5),
(8, 1),
(9, 6),
(10, 1),
...
(5000, 3);

Now you just have to use an update with an inner join : 

Update products p
INNER JOIN temp_products t_p ON t_p.id = p.id
SET p.category = t_p.category

if you want you can add a where clause : 

Update products p
INNER JOIN temp_products t_p ON t_p.id = p.id
SET p.category = t_p.category
WHERE p.category IS NULL

Upvotes: 3

Related Questions