Combinación interna de SQLite: actualice usando valores de otra tabla

Esto es bastante fácil y me lo han preguntado varias veces, pero no puedo hacerlo funcionar. La consulta SQL que creo debería funcionar es:

UPDATE table2 SET dst.a = dst.a + src.a, dst.b = dst.b + src.b, dst.c = dst.c + src.c, dst.d = dst.d + src.d, dst.e = dst.e + src.e FROM table2 AS dst INNER JOIN table1 AS src ON dst.f = src.f 

El uso de la statement de actualización no es posible porque en sqlite no se admiten las uniones en una statement de actualización. Ver documentos: statement de actualización

Si solo desea actualizar una sola columna a un valor estático, puede usar una subconsulta en la statement de actualización correctamente. Vea este ejemplo: ¿Cómo realizo una ACTUALIZACIÓN al unir tablas en SQLite?

Ahora en su ejemplo, suponiendo que hay una key única en la "columna f", una solución / solución que he encontrado es usar la instrucción replace:

 replace into table2 (a, b, c, d, e, f, g) select src.a, src.b, src.c, src.d, src.e, dest.f, dest.g from table1 src inner join table2 dest on src.f = dest.f 

También agregué una columna extra a la tabla 2 "columna g" para mostrar cómo "actualizarías" solo algunas de las columnas con este método.

Otra cosa de la que debe tener precaución es usar "PRAGMA foreign_keys = ON"; es posible tener problemas con esto ya que la fila se elimina e inserta de manera efectiva.

Se me ocurrió una técnica alternativa usando un GATILLO e "invirtiendo" la dirección de la actualización, aunque a costa de un campo ficticio en la tabla fuente.

En términos generales, tiene una tabla Master y una tabla de Updates . Desea actualizar algunos / todos los campos de loggings en el Master de los campos correspondientes en Updates vinculadas por un campo key Key .

En lugar de UPDATE Master SET ... FROM Master INNER JOIN Updates ON Mater.Key = Updates.Key hace lo siguiente:

  1. Agregue un campo ficticio TriggerField a la tabla Updates para actuar como el foco del desencadenador.

  2. Crea un disparador en este campo:

     CREATE TRIGGER UpdateTrigger AFTER UPDATE OF TriggerField ON Updates BEGIN UPDATE Master SET Field1 = OLD.Field1, Field2 = OLD.Field2, ... WHERE Master.Key = OLD.Key END; 
  3. Inicie el process de actualización con lo siguiente:

     UPDATE Updates SET TriggerField = NULL ; 

Notas

  1. El campo ficticio es simplemente un ancla para el desencadenador, de modo que cualquier otra UPDATE Updates SET ... no activará la actualización en Master . Si solo INSERT en Updates entonces no lo necesitas (y puedes eliminar la cláusula OF TriggerField al crear el activador).

  2. A partir de algunos times difíciles, parece funcionar a la misma velocidad que REPLACE INTO pero evita la técnica de eliminar y agregar filas. También es más sencillo si solo está actualizando algunos campos en Master ya que solo enumera los que desea cambiar.

  3. Es órdenes de magnitud más rápido que la otra alternativa que he visto UPDATE ... FROM que es:

     UPDATE Master SET Field1 = ( SELECT Field1 FROM Updates WHERE Mater.Key = Updates.Key ), Field1 = ( SELECT Field1 FROM Updates WHERE Mater.Key = Updates.Key ), ... ; 

    La actualización de seis campos en 1700 loggings fue de aproximadamente 0,05 para Tony y mis methods, pero de 2,50 para el método UPDATE ... ( SELECT... ) .

  4. AFTER UPDATE activadores de AFTER UPDATE en el Master parecen disparar como se esperaba.

Como dice Tony, la solución es replace el path, pero puedes usar el campo oculto de sqlite rowid para simular la actualización completa con join como:

 replace into table2 (rowid,a, b, c, d, e, f, g) select dest.rowid,src.a, src.b, src.c, src.d, src.e, dest.f, dest.g from table1 src inner join table2 dest on src.f = dest.f 

Con esto puedes recrear filas completas si no tienes la key principal para replace o como método estándar para hacer las actualizaciones con combinaciones.

SQLITE no admite UPDATE con INNER JOIN ni otros muchos DB. Las combinaciones internas son agradables y sencillas, sin embargo, se pueden realizar usando solo una ACTUALIZACIÓN y una selección de subconsulta. Al usar una cláusula where y la 'IN' con una subconsulta y una subconsulta adicional para 'SET', siempre se puede lograr el mismo resultado. A continuación se muestra cómo se hace.

 UPDATE table2 SET a = a + (select a from table1 where table1.f = table2.f), b = b + (select b from table1 where table1.f = table2.f), c = c + (select c from table1 where table1.f = table2.f), d = d + (select d from table1 where table1.f = table2.f), e = e + (select e from table1 where table1.f = table2.f) WHERE RowId IN (Select table2.RowId from table1 where table1.f = table2.f) 

Use la consulta a continuación:

 UPDATE table2 SET a = Za, b = Zb, c = Zc, d = Zd, e = Ze FROM (SELECT dst.id, dst.a + src.a AS a, dst.b + src.b AS b, dst.c + src.c AS c, dst.d + src.d AS d, dst.e + src.e AS e FROM table2 AS dst INNER JOIN table1 AS src ON dst.f = src.f )Z WHERE table2.id = z.id