303 votos

SQLite - UPSERT *no* INSERTAR o REEMPLAZAR

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

Inserción y Actualización de procedimientos almacenados en SQL Server

¿Hay alguna forma inteligente de hacer esto en SQLite que no he pensado?

Básicamente quiero actualizar tres de cada cuatro columnas si el registro existe, Si no existe yo quiero INSERTAR el registro con el valor por defecto (NULO) valor de la cuarta columna.

El ID es la clave principal para que haya sólo un registro a UPSERT.

(Estoy tratando de evitar la sobrecarga de SELECCIONAR en orden a determinar si es necesario ACTUALIZAR o INSERTAR obviamente)

Sugerencias?

553voto

Eric B Puntos 3926

Suponiendo 3 columnas en la tabla.. ID, NOMBRE, FUNCIÓN

Lo MALO: Esta va a insertar o reemplazar todas las columnas con los nuevos valores de ID=1:

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

Lo MALO: Esta va a insertar o reemplazar 2 de las columnas... el NOMBRE de la columna se establece en NULL o el valor predeterminado:

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

BUENO: Esta actualización 2 de las columnas. Cuando ID=1 existe, el NOMBRE no se verán afectados. Cuando ID=1 no existe, el nombre será por defecto (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 ID=1 existe, el PAPEL no se verán afectados. Cuando ID=1 no existe, la función se establece a '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')
          );

89voto

gregschlom Puntos 1824

INSERTAR O REEMPLAZAR es NO equivalente a "UPSERT".

Decir que tengo la tabla Empleados con los campos id, nombre y función:

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 papel y dejar el nombre.

75voto

Aristotle Pagaltzis Puntos 43253

Eric B, la respuesta es ACEPTAR si desea conservar sólo uno o tal vez dos columnas de la fila existente. Si desea conservar una gran cantidad de filas, se hace demasiado engorroso rápido.

Aquí, un enfoque que se adapta bien a cualquier cantidad de columnas a cada lado. Para ilustrar esto voy a suponer 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
 );

Nota en particular de que name es la clave de la fila - id sólo se utiliza para claves foráneas, así que el punto es para SQLite para recoger el valor de ID de sí mismo cuando se inserta una nueva fila. Pero a la hora de actualizar una fila existente, basado en su name, yo quiero seguir teniendo el antiguo valor de ID (obviamente!).

Puedo conseguir un cierto UPSERT con la siguiente construcción:

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

Aquí, si una fila no existía previamente old.id será NULO y SQLite asignará un ID de forma automática, pero si no ya estaba en una fila, old.id tendrá un valor real, y este va a ser reutilizado. Que es exactamente lo que yo quería.

En realidad esto no es muy flexible. Nota cómo la ts de columna falta completamente en todos los lados - porque tiene un DEFAULT valor, SQLite se acaba de hacer la cosa correcta en cualquier caso, así que no tengo que cuidar de mí mismo.

También puede incluir una columna en tanto el new y old lados y, a continuación, utilizar, por ejemplo. COALESCE(new.content, old.content) en el exterior SELECT que dice "inserte el nuevo contenido si la hubo, de lo contrario, mantenga el contenido antiguo" (por ejemplo. si usted está utilizando una consulta fija y de unión a los nuevos valores con los marcadores de posición.

57voto

Sam Saffron Puntos 56236

Si usted generalmente está haciendo actualizaciones de me ..

  1. Iniciar una transacción
  2. Hacer la actualización
  3. Comprobar el recuento de
  4. Si es 0 no hacer el insert
  5. Cometer

Si usted generalmente está haciendo inserta me gustaría

  1. Iniciar una transacción
  2. Intentar una inserción
  3. Verificación de la clave primaria de error de infracción de
  4. si tenemos un error hacer la actualización
  5. Cometer

De esta manera se evita la selección y que se transaccionalmente sonido en Sqlite.

39voto

Chris Stavropoulos Puntos 1061

Me doy cuenta de que este es un hilo viejo, pero he estado trabajando en sqlite3 como de tarde y vino para arriba con este método que mejor se ajustaba a mis necesidades de generación dinámica de consultas con parámetros:

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>; 

Todavía el 2 de consultas con una cláusula where en la actualización, pero parece que para hacer el truco. También tengo esta visión en mi cabeza de que sqlite puede optimizar lejos de la instrucción update completo si la llamada a los cambios() es mayor que cero. Si es o no hace en realidad que está más allá de mi conocimiento, pero un hombre puede soñar no? ;)

Para puntos de bonificación puede añadir esta línea, que se devuelve el id de la fila, tanto si es un recién insertado fila o en una fila existente.

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X