UPSERT *no* INSERTAR o REEMPLAZAR

http://en.wikipedia.org/wiki/Upsert

Insertar actualización del proceso almacenado en SQL Server

¿Existe alguna forma inteligente de hacer esto en SQLite en la que no haya pensado?

Básicamente quiero actualizar tres de cuatro columnas si el registro existe. Si no existe, quiero INSERTAR el registro con el valor predeterminado (NULL) para la cuarta columna.

La identificación es una clave principal, por lo que solo habrá un registro para UPSERT.

(Estoy tratando de evitar la sobrecarga de SELECCIONAR para determinar si necesito ACTUALIZAR o INSERTAR obviamente)

¿Sugerencias?

No puedo confirmar esa sintaxis en el sitio SQLite para TABLE CREATE. No he creado una demostración para probarlo, pero no parece ser compatible.

Si así fuera, tengo tres columnas, por lo que en realidad se vería así:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

pero los dos primeros blobs no causarán un conflicto, solo el ID lo haría. Así que supongo que Blob1 y Blob2 no serían reemplazados (como se desea)

Las ACTUALIZACIONES en SQLite cuando los datos vinculantes son una transacción completa, lo que significa que cada fila enviada para actualizarse requiere: declaraciones de preparación/vinculación/paso/finalización, a diferencia de INSERT, que permite el uso de la función de reinicio.

La vida de un objeto de declaración es más o menos así:

Crea el objeto usando sqlite3_prepare_v2()
Vincule valores a parámetros del host utilizando las interfaces sqlite3_bind_.
Ejecute SQL llamando a sqlite3_step()
Restablezca la declaración usando sqlite3_reset(), luego regrese al paso 2 y repita.
Destruya el objeto de declaración usando sqlite3_finalize().

ACTUALIZAR Supongo que es lento en comparación con INSERTAR, pero ¿cómo se compara con SELECCIONAR usando la clave principal?

¿Quizás debería usar la selección para leer la cuarta columna (Blob3) y luego usar REEMPLAZAR para escribir un nuevo registro que combine la cuarta columna original con los nuevos datos para las primeras 3 columnas?

Jan 07 '09 08:01 Mike Trader

Suponiendo tres columnas en la tabla: ID, NOMBRE, ROL

MALO: Esto insertará o reemplazará todas las columnas con nuevos valores para ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

MALO: Esto insertará o reemplazará 2 de las columnas... la columna NOMBRE se establecerá en NULL o el valor predeterminado:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

BUENO : Utilice SQLite en la cláusula de conflicto ¡Soporte UPSERT en SQLite! ¡La sintaxis UPSERT se agregó a SQLite con la versión 3.24.0!

UPSERT es una adición de sintaxis especial a INSERT que hace que INSERT se comporte como una ACTUALIZACIÓN o como una operación no operativa si INSERT viola una restricción de unicidad. UPSERT no es SQL estándar. UPSERT en SQLite sigue la sintaxis establecida por PostgreSQL.

ingrese la descripción de la imagen aquí

BUENO pero tedioso: esto actualizará 2 de las columnas. Cuando existe ID=1, el NOMBRE no se verá afectado. Cuando ID=1 no existe, el nombre será el predeterminado (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

Esto actualizará 2 de las columnas. Cuando existe ID=1, el ROLE no se verá afectado. Cuando ID=1 no existe, la función se establecerá en 'Benchwarmer' en lugar del valor predeterminado.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Dec 02 '2010 00:12 Eric B

INSERTAR O REEMPLAZAR NO es equivalente a "UPSERT".

Digamos que tengo la tabla Empleado con los campos id, nombre y rol:

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

Boom, has perdido el nombre del empleado número 1. SQLite lo ha reemplazado con un valor predeterminado.

El resultado esperado de un UPSERT sería cambiar el rol y mantener el nombre.

Nov 23 '2010 07:11 gregschlom

La respuesta de Eric B está bien si desea conservar solo una o quizás dos columnas de la fila existente. Si desea conservar muchas columnas, rápidamente se vuelve demasiado engorroso.

Este es un enfoque que se adaptará bien a cualquier cantidad de columnas en cada lado. Para ilustrarlo asumiré el siguiente esquema:

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

Tenga en cuenta en particular que namees la clave natural de la fila; idse usa solo para claves externas, por lo que el punto es que SQLite elija el valor de ID al insertar una nueva fila. Pero al actualizar una fila existente en función de su name, quiero que siga teniendo el valor de ID anterior (¡obviamente!).

Logro un verdadero UPSERTcon la siguiente construcción:

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

La forma exacta de esta consulta puede variar un poco. La clave es el uso de INSERT SELECTcon una unión externa izquierda, para unir una fila existente a los nuevos valores.

Aquí, si una fila no existía anteriormente, old.idlo será NULLy SQLite asignará una ID automáticamente, pero si ya existía dicha fila, old.idtendrá un valor real y este se reutilizará. Que es exactamente lo que quería.

De hecho, esto es muy flexible. Observe cómo la tscolumna falta por completo en todos los lados; debido a que tiene un DEFAULTvalor, SQLite hará lo correcto en cualquier caso, por lo que no tengo que ocuparme de ello yo mismo.

También puede incluir una columna en ambos newlados oldy luego usar, por ejemplo, COALESCE(new.content, old.content)en el exterior SELECTpara decir "inserte el nuevo contenido si lo hubiera; de lo contrario, conserve el contenido anterior", por ejemplo, si está utilizando una consulta fija y está vinculando el nuevo. valores con marcadores de posición.

Sep 22 '2011 08:09 Aristotle Pagaltzis

Esta respuesta se ha actualizado y, por lo tanto, los comentarios a continuación ya no se aplican.

2018-05-18 DETENER PRENSA.

Soporte UPSERT en SQLite! ¡La sintaxis UPSERT se agregó a SQLite con la versión 3.24.0 (pendiente)!

UPSERT es una adición de sintaxis especial a INSERT que hace que INSERT se comporte como una ACTUALIZACIÓN o como una operación no operativa si INSERT viola una restricción de unicidad. UPSERT no es SQL estándar. UPSERT en SQLite sigue la sintaxis establecida por PostgreSQL.

ingrese la descripción de la imagen aquí

alternativamente:

Otra forma completamente diferente de hacer esto: en mi aplicación configuro mi ID de fila en la memoria para que sea long.MaxValue cuando creo la fila en la memoria. (MaxValue nunca se usará como ID, no vivirá lo suficiente...) Entonces, si RowID no es ese valor, entonces ya debe estar en la base de datos, por lo que necesita una ACTUALIZACIÓN, si es MaxValue, entonces necesita una inserción. Esto solo es útil si puedes rastrear los ID de fila en tu aplicación.

Aug 28 '2015 12:08 AnthonyLambert

Si generalmente estás haciendo actualizaciones, yo ...

Comenzar una transacción
hacer la actualizacion
Verifique el recuento de filas
Si es 0 haz el inserto
Comprometerse

Si generalmente haces inserciones, lo haría

Comenzar una transacción
Pruebe un inserto
Compruebe si hay un error de infracción de clave principal
si recibimos un error, hagamos la actualización.
Comprometerse

De esta manera evitas la selección y eres transaccionalmente sólido en Sqlite.

Jan 07 '2009 02:01 Sam Saffron