Updating multiple Mysql lines and fields

Question

I built a Mysql Query in order to update a field in several rows of my Mysql table. It was as follows:

update produtos set (nome) = case codigo
when 5 then 'teste 1'
when 6 then 'teste 2'
when 7 then 'teste 3'
where codigo in (5,6,7)

Worked perfectly.

I would, however, like to update in parallel other fields, such as quantity. As adequo the following query above to make this possible?

Answer 1

Just put a comma to each field:

UPDATE produtos SET
    nome = CASE codigo
        WHEN 5 THEN 'teste 1'
        WHEN 6 THEN 'teste 2'
        WHEN 7 THEN 'teste 3'
    END, campo2 = "valor2", campo3 = "valor3"
WHERE codigo IN (5,6,7);

That one END before the comma, means the end of the CASE, avoid forgetting you to not receive the following error:

Error Code: 1064 You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near

Answer 2

Another solution I found in the link below using "ON DUPLICATE KEY UPDATE":

https://tableplus.io/blog/2018/11/how-to-update-multiple-rows-at-once-in-mysql.html

Below is the example code adapted for your solution:

INSERT INTO repostas 
    (id, resposta)
    VALUES 
        (1, "$_POST['1']"),
        (2, "$_POST['2']"),
        (3, "$_POST['3']"),
        (4, "$_POST['4']")
    ON DUPLICATE KEY UPDATE 
        resposta = VALUES(resposta);