database - SQLite UPSERT/UPDATE O INSERT

sqlite insert or replace

Question

Necesito realizar UPSERT / INSERT O UPDATE contra una base de datos SQLite.

Existe el comando INSERT O REPLACE que en muchos casos puede ser útil. Pero si desea mantener sus identificaciones con autoincrement debido a claves externas, no funciona, ya que elimina la fila, crea una nueva y, por consiguiente, esta nueva fila tiene una nueva ID.

Esta sería la mesa:

jugadores - (clave principal en id, nombre_usuario único)

|  id   | user_name |  age   |
------------------------------
|  1982 |   johnny  |  23    |
|  1983 |   steven  |  29    |
|  1984 |   pepee   |  40    |

Answer 1

Opción 1: Insertar -> Actualizar

Si desea evitar ambos changes()=0 e INSERT OR IGNORE incluso si no puede permitirse borrar la fila: puede usar esta lógica;

Primero, inserte (si no existe) y luego actualice filtrando con la clave única.

Ejemplo

-- Table structure
CREATE TABLE players (
    id        INTEGER       PRIMARY KEY AUTOINCREMENT,
    user_name VARCHAR (255) NOT NULL
                            UNIQUE,
    age       INTEGER       NOT NULL
);

-- Insert if NOT exists
INSERT INTO players (user_name, age)
SELECT ''johnny'', 20
WHERE NOT EXISTS (SELECT 1 FROM players WHERE user_name=''johnny'' AND age=20);

-- Update (will affect row, only if found)
-- no point to update user_name to ''johnny'' since it''s unique, and we filter by it as well
UPDATE players 
SET age=20 
WHERE user_name=''johnny'';

En cuanto a los disparadores

Aviso: no lo he probado para ver qué disparadores se están llamando, pero asumo lo siguiente:

si la fila no existe

• ANTES DE INSERTAR
• INSERT usando INSTEAD OF
• DESPUÉS DE INSERTAR
• ANTES DE ACTUALIZAR
• ACTUALIZACIÓN usando EN LUGAR DE
• DESPUÉS DE LA ACTUALIZACIÓN

si la fila existe

• ANTES DE ACTUALIZAR
• ACTUALIZACIÓN usando EN LUGAR DE
• DESPUÉS DE LA ACTUALIZACIÓN

Opción 2: inserta o reemplaza: guarda tu propia identificación

de esta forma puedes tener un solo comando SQL

-- Table structure
CREATE TABLE players (
    id        INTEGER       PRIMARY KEY AUTOINCREMENT,
    user_name VARCHAR (255) NOT NULL
                            UNIQUE,
    age       INTEGER       NOT NULL
);

-- Single command to insert or update
INSERT OR REPLACE INTO players 
(id, user_name, age) 
VALUES ((SELECT id from players WHERE user_name=''johnny'' AND age=20),
        ''johnny'',
        20);

Editar: opción agregada 2.

Answer 2

Q & A Style

Bueno, después de investigar y luchar con el problema durante horas, descubrí que hay dos formas de lograr esto, dependiendo de la estructura de su tabla y si tiene restricciones de claves externas activadas para mantener la integridad. Me gustaría compartir esto en un formato limpio para ahorrar algo de tiempo a las personas que puedan estar en mi situación.

Opción 1: puede permitirse eliminar la fila

En otras palabras, no tiene una clave externa, o si la tiene, su motor SQLite está configurado para que no haya excepciones de integridad. El camino a seguir es INSERTAR O REEMPLAZAR . Si está intentando insertar / actualizar un reproductor cuya ID ya existe, el motor de SQLite eliminará esa fila e insertará los datos que está proporcionando. Ahora viene la pregunta: ¿qué hacer para mantener el antiguo ID asociado?

Digamos que queremos UPSERT con los datos user_name = ''steven'' y age = 32.

Mira este código:

INSERT INTO players (id, name, age)

VALUES (
    coalesce((select id from players where user_name=''steven''),
             (select max(id) from drawings) + 1),
    32)

El truco está en fusionarse. Devuelve el id del usuario ''steven'' si lo hay, y de lo contrario, devuelve una nueva identificación nueva.

Opción 2: no puede permitirse eliminar la fila

Después de molestarme con la solución anterior, me di cuenta de que en mi caso eso podría terminar destruyendo datos, ya que esta ID funciona como una clave foránea para otra tabla. Además, creé la tabla con la cláusula ON DELETE CASCADE , lo que significaría que eliminaría los datos de forma silenciosa. Peligroso.

Entonces, primero pensé en una cláusula IF, pero SQLite solo tiene CASE . Y este CASO no se puede usar (o al menos no lo pude) para realizar una consulta de ACTUALIZACIÓN si EXISTE (seleccione id de reproductores donde nombre_de_usuario = ''steven''), e INSERTAR si no lo hizo. No vayas.

Y luego, finalmente utilicé la fuerza bruta, con éxito. La lógica es que, para cada UPSERT que desee realizar, primero ejecute INSERT O IGNORE para asegurarse de que haya una fila con nuestro usuario, y luego ejecute una consulta UPDATE con exactamente los mismos datos que intentó insertar.

Los mismos datos que antes: user_name = ''steven'' y age = 32.

-- make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES (''steven'', 32); 

-- make sure it has the right data
UPDATE players SET user_name=''steven'', age=32 WHERE user_name=''steven''; 

¡Y eso es todo!

EDITAR

Como ha comentado Andy, intentar insertar primero y luego actualizar puede provocar disparadores con más frecuencia de la esperada. Esto no es, en mi opinión, un problema de seguridad de datos, pero es cierto que disparar eventos innecesarios tiene poco sentido. Por lo tanto, una solución mejorada sería:

-- Try to update any existing row
UPDATE players SET user_name=''steven'', age=32 WHERE user_name=''steven'';

-- Make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES (''steven'', 32); 

Answer 3

El problema con todas las respuestas presentadas completa la falta de desencadenantes de toma (y probablemente otros efectos secundarios) en cuenta. Solución como

INSERT OR IGNORE ...
UPDATE ...

conduce a ambos disparadores ejecutados (para insertar y luego para actualizar) cuando la fila no existe.

La solución adecuada es

UPDATE OR IGNORE ...
INSERT OR IGNORE ...

en ese caso, solo se ejecuta una declaración (cuando la fila existe o no).

Answer 4

Esta es una respuesta tardía. A partir de SQLIte 3.24.0, lanzado el 4 de junio de 2018, finalmente hay un soporte para la cláusula UPSERT siguiendo la sintaxis de PostgreSQL.

INSERT INTO players (user_name, age)
  VALUES(''steven'', 32) 
  ON CONFLICT(user_name) 
  DO UPDATE SET age=excluded.age;

Answer 5

Para tener un UPSERT puro sin agujeros (para programadores) que no transmiten en claves únicas y otras:

UPDATE players SET user_name="gil", age=32 WHERE user_name=''george''; 
SELECT changes();

SELECT changes () devolverá el número de actualizaciones realizadas en la última consulta. Luego, verifique si el valor de retorno de changes () es 0, de ser así, ejecute:

INSERT INTO players (user_name, age) VALUES (''gil'', 32); 

Answer 6

También puede agregar una cláusula ON CONFLICT REPLACE a su restricción exclusiva user_name y luego simplemente INSERT away, dejándolo en SQLite para que sepa qué hacer en caso de conflicto. Ver: https://sqlite.org/lang_conflict.html .

También tenga en cuenta la oración con respecto a los desencadenantes de eliminación: cuando la estrategia de resolución de conflictos REPLACE elimina las filas para satisfacer una restricción, eliminar desencadena el disparo solo si se activan activadores recursivos.

Answer 7

Te haré uno mejor que no requiera la fuerza bruta ''ignorar'', que solo funcionaría si hubiera una violación clave. De esta manera, funciona según las condiciones que especifique en la actualización.

Prueba esto...

-- Try to update any existing row
UPDATE players
SET user_name=''steven'', age=32
WHERE user_name=''steven'';

-- If no update happened (i.e. the row didn''t exist) then insert one
INSERT INTO players (user_name, age)
SELECT ''steven'', 32
WHERE (Select Changes() = 0);

Cómo funciona

La ''magia'' aquí es utilizar la cláusula Where (Select Changes() = 0) para determinar si hay filas para la inserción, y como está basada en su propia cláusula Where , puede ser para cualquier cosa que defina, no solo violaciones clave.

En el ejemplo anterior, si no hay cambios desde la actualización (es decir, el registro no existe), entonces Changes() = 0 entonces la cláusula Where en la instrucción Insert devuelve verdadero y se inserta una nueva fila con los datos especificados.

Si la Update actualizó una fila existente, entonces Changes() = 1, por lo que la cláusula ''Where'' en el Insert ahora será falsa y, por lo tanto, no se realizará ninguna inserción.

No se necesita fuerza bruta.

Answer 8

La respuesta aceptada no es correcta
Debido a su consulta 2
¡es complejo solo!


esto es simple 2 consulta:

$check=query(''select id from players where user_name="steven";'');

if(empty($check))
{
   query(''insert into players (user_name,age) values ("steven",32);'');
}
else
{
   query(''update players set age=13 where id=''.$check[''id''].'';'');
}

• consulta es una función por ejemplo