UPDATE in a query with multiple entries

Question

I have a query where I have a SELECT from a client ID, and an order ID. Later I perform an UPDATE in a table, changing the tax rate on the selected order.

The problem is that a customer may have multiple orders. When I place several order Ids.... But how can I actually do a query to update these multiple entries?

Here I have the SELECT:

SELECT
  p.idPedido,
  c.nome,
  p.dataPagamento,
  p.valor,
  p.taxaImposto
FROM
  Pedidos p
JOIN Clientes c ON (c.id = p.idCliente)
WHERE
  c.id IN ({txtClienteId.Text})
AND
  p.idPedido IN ({txtPedidoId.Text})

And here the update:

UPDATE
  Pedidos
SET
  taxaImposto = @taxa
WHERE
  idPedido = @idPedido
AND
  idCliente = @idCliente;

cmd.Parameters.Add("@taxa", SqlDbType.Float).Value = Convert.ToDouble(txtTaxaImposto.Text);
cmd.Parameters.Add("@idPedido", SqlDbType.BigInt).Value = Convert.ToInt32(txtPedidoId.Text);
cmd.Parameters.Add("@idCliente", SqlDbType.BigInt).Value = Convert.ToInt32(txtClienteId.Text);

UPDATE works normally when I only have one entry in the Orders ID, but I want to make it possible to update to multiple entries, from the same customer, that is, insert a customer ID, a Fee, and n Orders ID’s.

Answer 1

Taking into account the information on the issue and comments, maybe this way you can solve the problem:

string strSql = string.Format(@"
                UPDATE  Pedidos
                SET     taxaImposto = @taxa
                WHERE   idPedido    IN ({0})
                    AND idCliente   = @idCliente", txtPedidoId.Text);

// A variável "strSql" será utilizada para criar o "SqlCommand"

cmd.Parameters.Add("@taxa", SqlDbType.Float).Value = Convert.ToDouble(txtTaxaImposto.Text);
cmd.Parameters.Add("@idCliente", SqlDbType.BigInt).Value = Convert.ToInt32(txtClienteId.Text);

Basically, the UPDATE will be executed for a specific client (as it indicated that the ID the customer is indicated and the orders will be for that same customer) but for several orders (those indicated in txtPedidoId.Text).

Answer 2

Good morning, you are doing the update and select separately, to perform all the updates at the same time you could perform the update and select in a single query

UPDATE
  Pedidos
SET
  Pedidos.taxaImposto = {txtTaxaImposto.Text}
FROM
  Pedidos p
  INNER JOIN Clientes c ON (c.id = p.idCliente)
WHERE
  c.id IN ({txtClienteId.Text})
AND
  p.idPedido IN ({txtPedidoId.Text})

In this case you can put the order list in the field {txtPedidoId.Text} and he would be responsible for updating all orders on this list.