Update of several records together with select

Asked

Viewed 2,202 times

2

I need to do UPDATE with SELECT for several records, today I am 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 need to do all the records I put inside the IN ('2763','2768','2802', 'etc').

Has anyone ever been in a situation or has any idea how to do?

mysql sql sql-server

2 answers

2


William, the topic is marked with Mysql and SQL Server tags. As you know, not always does an SQL build 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 need to select inside the update for each different record. Yes the round works too. Plus I had to use Ceiling. I can’t remember why. But I had a result that wasn’t expected.

  • In case I use this query in a sql server to serve only one client. I marked mysql because some clients are with mysql and in the future I will implement. I know that all constructions do not work in all databases. More are similar. And just need to adapt.

  • Willian, the internal SELECT is not necessary if what you need is only to calculate the contents of the "value" column from the "liter" column, having defined in the WHERE clause for which lines this action must be performed. Regarding the use of Ceiling(), you may have chosen it instead of round(), so that the value is always rounded up.

  • UPDATE banco.ordem 
 set valor = round((litros * 3.67), 2, 0)
 where ordens in ('2763','2768','2802', ...); It will fetch the value of the colula and multiply and update?

  • I had to adapt, but it really worked and your answer and the most correct for the situation.

  • Thank you. Thanks

Show 1 more comment

2

I don’t know if I got it right, but come on.

Your subselect may not contain the IN , you will have to put an alias in the table you want to update and use this alias to access the current update row value 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 wasn’t clear to me where the column "orders".

I hope it helped.

  • The order column does not exist. Bank.order is the bank and the table. In this case the row is being selected by the order column which in this case is like the id.

  • Got it. Orders and sequential. It’s within the order table. Plus I’m only working from a table.

  • It worked, I just had to change banco.ordem ordem1 for banco.ordem AS ordem1 and WHERE ordens = ordem1.ordem for WHERE ordens = ordem1.ordens. I ordered the edition, but I don’t know if it will approve. anything edit. Thank you very much brother.

Browser other questions tagged mysql sql sql-server

You are not signed in. Login or sign up in order to post.