Как увеличить поле, если запись уже существует?

Шаги сценария.

Шаг 1. Создайте temp таблицу:

CREATE TABLE IF NOT EXISTS temp (…);

Шаг 2. Создайте dump таблицу:

CREATE TABLE IF NOT EXISTS dump (…);

Шаг 3. Откройте транзакцию.

BEGIN TRANSACTION;

Шаг 4. Очистите таблицу:

DELETE FROM temp;

Шаг 5. Импортируйте данные в таблицу temp.

Шаг 6. Объедините данные с помощью Upsert:

INSERT INTO dump (…)
SELECT … FROM temp
ON CONFLICT(first, last) DO UPDATE SET
seen = seen + 1
…

Шаг 7. Зафиксируйте изменения.

COMMIT;

У вас есть сценарий Bash, который импортирует CSV в базу данных SQLite. CSV постоянно обновляется, и вам необходимо увеличивать поле seen для существующих записей при вставке новых.

Решение: эффективный UPSERT с транзакциями SQLite

Чтобы эффективно справиться с этим, мы будем использовать синтаксис INSERT OR REPLACE SQLite внутри транзакции.

cat <<EOF | sqlite3 "$1.db"
BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS 'dump' (
    pk INTEGER PRIMARY KEY,
    first TEXT,
    last TEXT,
    seen INTEGER DEFAULT 1,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(first, last)
);

.mode csv
.separator ";"

-- Create a temporary table with necessary columns
CREATE TEMP TABLE tmp_import (
    first TEXT,
    last TEXT
);

-- Import the CSV into the temporary table
.import '$1.tmp' tmp_import;

-- Efficiently insert or update records
INSERT OR REPLACE INTO dump (first, last, seen)
SELECT first, last, seen + 1
FROM tmp_import;

DROP TABLE tmp_import;
COMMIT;
EOF

Объяснение:

• Начать транзакцию: обеспечивает согласованность данных, особенно для одновременных обновлений.
• Создать таблицу: создает таблицу dump, если она не существует.
• Временная таблица: создает временную таблицу для эффективной обработки.
• Импортировать CSV: импортирует данные CSV во временную таблицу.
• UPSERT: использует INSERT OR REPLACE для вставки новых записей или обновления. существующие, увеличивая количество seen.
• Удалить временную таблицу: очищает временную таблицу.
• Зафиксировать транзакцию: фиксирует изменения в базе данных.

На основе ответа Md Mahfuz RP с исправленной и проверенной рабочей реализацией.

#!/usr/bin/env sh

sqlite3 "$1.db" <<SQL
CREATE TABLE IF NOT EXISTS 'dump' (
    pk INTEGER PRIMARY KEY,
    first TEXT,
    last TEXT,
    seen INTEGER DEFAULT 0,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(first, last)
);

.mode csv
.separator ";"

-- Create a temporary table in memory with necessary columns
PRAGMA temp_store = 2; -- 2 means use in-memory
DROP TABLE IF EXISTS tmp_import;
CREATE TEMP TABLE tmp_import (
    first TEXT,
    last TEXT
);

-- Import the CSV into the temporary table
.import '$1.csv' tmp_import

-- Efficiently insert or update records as a single transaction
BEGIN TRANSACTION;

-- Imports new lines from tmp_import if they don't exist in dump
INSERT OR IGNORE INTO dump (first, last)
SELECT first, last
FROM tmp_import;

-- Increment the seen of dump's lines that are in tmp_import
UPDATE dump
SET seen = seen + 1
FROM (
  SELECT first, last FROM tmp_import
) AS t
WHERE t.first = dump.first AND t.last = dump.last;

COMMIT;

-- Dispose of the in-memory tmp_import table
DROP TABLE IF EXISTS tmp_import;
SQL