Question 292777 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Update SQL Cells

I am searching for an easy solution. I wanna Update 800 rows of data. There is no unique ID so the ON DUPLICATE KEY Update is not possible.

I only show 3 rows of data, but there are over 800. So i wanna update the quantity of a product list directly in the database.

I tried like this, but that did not work and i don’t know why. My code is not working.

UPDATE `xt_products` SET `products_quantity` = [Value-2] WHERE `products_model`= [Value-1]
('2','0'),
('3','1'),
('32','25'),

value-2 means the second input of the data-row (0,1,25)
value-1 means the first input of the data-row (2,3,32)

When i do only one row it works:

UPDATE `xt_products` SET `products_quantity` = "25" WHERE `products_model`= "32"

The Column products_model:
products_model varchar(255) utf8mb4_general_ci NULL

But how i get the data into the command? value-1 and value-2 ?

I tried different ways for a solution. But i didn’t find one.

I wanna update directly in the sql-command field at the database.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Seems not so easy to find a solution. So i did it my way. It is easier to change the file with the right program. I switched quality and quantity in the sequence (really easy from the output program). Then i do a prefix in each line

    UPDATE `xt_products` SET `products_quantity` =

    i replaced the ; between the numbers with

     WHERE `products_model`=

    and the suffix in each line ,

    Result:

     UPDATE `xt_products` SET `products_quantity` = "0" WHERE `products_model`= "2"; 
UPDATE `xt_products` SET `products_quantity` = "1" WHERE `products_model`= "3";
UPDATE `xt_products` SET `products_quantity` = "-28" WHERE `products_model`= "4";
UPDATE `xt_products` SET `products_quantity` = "0" WHERE `products_model`= "5";
UPDATE `xt_products` SET `products_quantity` = "0" WHERE `products_model`= "7";
UPDATE `xt_products` SET `products_quantity` = "4" WHERE `products_model`= "8";
UPDATE `xt_products` SET `products_quantity` = "0" WHERE `products_model`= "9";
UPDATE `xt_products` SET `products_quantity` = "0" WHERE `products_model`= "10";

    Around 800 rows of simple Update code. And it works. Took me 1min to insert prefix, replace and suffix.

    I know, it is not the nice way, but simple and effective for me.


  2. 0

    Based on the example you gave, you can do:

    UPDATE `xt_products` 
INNER JOIN (SELECT * 
            FROM (VALUES ROW('2','0'),
                         ROW('3','1'),
                         ROW('32','25')) as y) as x ON 
                      x.column_0 = `xt_products`.products_model
                      AND x.column_1 = `xt_products`.products_quantity
SET `products_quantity` = x.column_1 WHERE `products_model`= x.column_2

    Short explanation:

    The query:

    SELECT * 
FROM (VALUES ROW('2','0'),
             ROW('3','1'),
             ROW('32','25')) y

    returns:

    column_0 column_1
    2 0
    3 1
    32 25
    • this result is INNER JOINed to xt_products
    • the results from this inner join can be used to update the needed fields.

    More info:

    NOTE:

    • When using products_quantity, this fields seems to be numeric. When using numeric data in MySQL, you should not use quotes around the value, so you should do ROW('2',0), and not ROW('2','0')
    • When you say (in your comments) "product_model could also be like "T23WX"", you should not (NEVER) use over simplified example data like '1', or '2'. Just use the REAL names there, or if that is not possible use flower names, or planet names, or any other anonymus list.
    • From Tips for asking a good Structured Query Language (SQL) question, point 2A, 2B and 3 are important! (for a mre)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search