Can i UPDATE not insert, multiple values for one row with diferente conditions?

Question

i need to update my table document_Format that has a Column named NumVias, and the table has a row called CodFormatoCategoria . What i have is this:

'UPDATE document_Format
                SET NumVias = "'.$Campos['NumVias_Vendas'].'" 
                WHERE CodFormatoCategoria = "VEND" and CodEmpresa = "'.$CodEmpresa.'" '

What i need is like:

'UPDATE CodFormatoCategoria 
                SET NumVias = "'.$Campos['NumVias_Vendas'].'" 
                WHERE CodFormatoCategoria = "VEND",

                SET NumVias = "'.$Campos['NumVias_Compras'].'" 
                WHERE CodFormatoCategoria = "COMP",

                SET NumVias = "'.$Campos['NumVias_Diversos'].'" 
                WHERE CodFormatoCategoria = "DIV",

 and CodEmpresa = "'.$CodEmpresa.'" '

IS this possible without running the function 3 times? Can i do it in one query?

Have a look https://stackoverflow.com/questions/8348037/mysql-case-where-then-statements — Sougata Bose, Jul 31 '19 at 11:12

score 3 · Accepted Answer · answered Jul 31 '19 at 11:15

You can use a CASE statement to conditionally choose a value for the column. If there were no matches for any CASE WHEN statement, you can set an ELSE statement to keep the original value, so nothing changes.

$sql = "UPDATE CodFormatoCategoria 
           SET NumVias = CASE WHEN CodFormatoCategoria = 'VEND' THEN '".$Campos['NumVias_Vendas']."' 
                              WHEN CodFormatoCategoria = 'COMP' THEN '".$Campos['NumVias_Compras']."' 
                              WHEN CodFormatoCategoria = 'DIV' THEN '".$Campos['NumVias_Diversos']."' 
                              ELSE NumVias 
                          END
            WHERE CodEmpresa = '".$CodEmpresa."'";

Thanks Qirel that worked great! Ive tried that before but i was having some syntax issues! Thanks alot! — Alexis Garcia, Jul 31 '19 at 11:24

Can i UPDATE not insert, multiple values for one row with diferente conditions?

1 Answers1