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é).