I need to do UPDATE with SELECT for multiple records, today I'm with this query:
UPDATE banco.ordem SET valor = (SELECT (CEILING((litros * 3.67)*100)/100) AS valor_litros FROM banco.ordem WHERE ordens = 2763) WHERE ordens = 2763
This query updates only one record, I have to make all the records I put inside the IN ('2763','2768','2802', 'etc') .
Has anyone ever had a situation or do you have any idea how to do it?
William, the topic is marked with the MySQL and SQL Server tags. As you know, a SQL build does not always work on all sgbd.
In T-SQL (SQL Server) the solution can be simplified to something like
-- código #1
UPDATE banco.ordem
set valor = round((litros * 3.67), 2, 0)
where ordens in ('2763','2768','2802', ...);
Note that the round () function was used to round the result to two decimal places. The result is similar to the use of ceiling (x * 100) / 100.
PS : Using the ceiling function, we have:
-- código #2
UPDATE banco.ordem
set valor = ceiling( (litros * 3.67) * 100) / 100)
where ordens in ('2763','2768','2802', ...);
I do not know if I understand correctly, but come on.
Your subselect can not contain IN , you will have to put an alias in the table that wants to update and use that alias to access the value of the current update row in your subselect.
Your IN should only exist in the update where.
UPDATE banco.ordem AS ordem1
SET valor = (SELECT (CEILING((litros * 3.67)*100)/100) AS valor_litros FROM banco.ordem WHERE ordens = ordem1.ordens)
WHERE ordens in ('2763','2768','2802', 'etc')
Maybe you need to adapt something, because it was not clear to me where the column "orders".
I hope you have helped.