values - Actualizar múltiples filas con diferentes valores en una sola consulta SQL

sentencia para insertar un registro en sql

Question

Tengo una base de datos SQLite con tabla myTable y columnas id , posX , posY . El número de filas cambia constantemente (puede aumentar o disminuir). Si conozco el valor de id para cada fila y el número de filas, ¿puedo realizar una única consulta SQL para actualizar todos los campos posX y posY con diferentes valores según el id?

Por ejemplo:

---------------------
myTable:

id   posX    posY

1      35     565
3      89     224
6      11     456
14     87     475
---------------------

Pseudocódigo de consulta SQL:

UPDATE myTable SET posX[id] = @arrayX[id], posY[id] = @arrayY[id] "

@arrayX y @arrayY son matrices que almacenan nuevos valores para los campos posX y posY .

Si, por ejemplo, arrayX y arrayY contienen los siguientes valores:

arrayX = { 20, 30, 40, 50 }
arrayY = { 100, 200, 300, 400 }

... entonces la base de datos después de la consulta debería verse así:

---------------------
myTable:

id   posX    posY

1      20     100
3      30     200
6      40     300
14     50     400
---------------------

es posible? Estoy actualizando una fila por consulta ahora mismo, pero tomará cientos de consultas a medida que aumente el número de filas. Estoy haciendo todo esto en AIR por cierto.

Answer 1

Algo como esto podría funcionar para usted:

"UPDATE myTable SET ... ;
 UPDATE myTable SET ... ;
 UPDATE myTable SET ... ;
 UPDATE myTable SET ... ;"

Si cualquiera de los valores posX o posY son los mismos, entonces podrían combinarse en una consulta

UPDATE myTable SET posX=''39'' WHERE id IN(''2'',''3'',''40'');

Answer 2

Hay un par de maneras de lograr esto decentemente eficientemente.

Primero -
Si es posible, puede hacer algún tipo de inserción masiva en una tabla temporal. Esto depende en cierta medida de su RDBMS / lenguaje de host, pero en el peor de los casos, esto se puede lograr con un SQL dinámico simple (utilizando una cláusula VALUES() ), y luego una tabla de actualización de otro estándar. La mayoría de los sistemas proporcionan utilidades para carga masiva, aunque

Segundo -
Y esto también es algo dependiente de RDBMS, podría construir una instrucción de actualización dinámica. En este caso, donde la cláusula VALUES(...) dentro del CTE se ha creado sobre la marcha:

WITH Tmp(id, px, py) AS (VALUES(id1, newsPosX1, newPosY1), 
                               (id2, newsPosX2, newPosY2),
                               ......................... ,
                               (idN, newsPosXN, newPosYN))

UPDATE TableToUpdate SET posX = (SELECT px
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id),
                         posY = (SELECT py
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id)


WHERE id IN (SELECT id
             FROM Tmp)

(De acuerdo con la documentación, esta debe ser una sintaxis SQLite válida, pero no puedo hacer que funcione en un violín)

Answer 3

Pruebe con "actualizar el conjunto de tabletas (fila = ''valor'' donde id = 0001 ''), (fila ='' valor2 ''donde id = 0002''), ...

Answer 4

Sí, puede hacer esto, pero dudo que mejore el rendimiento, a menos que su consulta tenga una latencia muy grande.

Podrías hacerlo:

 UPDATE table SET posX=CASE
      WHEN id=id[1] THEN posX[1]
      WHEN id=id[2] THEN posX[2]
      ...
      ELSE posX END, posY = CASE ... END
 WHERE id IN (id[1], id[2], id[3]...);

El costo total se da más o menos por: NUM_QUERIES * (COST_QUERY_SETUP + COST_QUERY_PERFORMANCE). De esta manera, derribas un poco en NUM_QUERIES, pero COST_QUERY_PERFORMANCE aumenta en grande. Si COST_QUERY_SETUP es realmente enorme (por ejemplo, está llamando a algún servicio de red que es muy lento) entonces, sí, aún podría terminar en la cima.

De lo contrario, lo intentaría con la indexación en ID o la modificación de la arquitectura.

En MySQL, creo que podría hacer esto más fácilmente con una actualización múltiple INSERT ON DUPLICATE KEY UPDATE (pero no estoy seguro, nunca lo intenté).