Скопировать выделение из двух связанных таблиц

У меня есть две таблицы в базе данных. В первой таблице (tab1) у меня есть список элементов. Во второй таблице у меня есть много разных отношений между этими элементами.

CREATE TABLE tab1(id INTEGER PRIMARY KEY ASC,set INTEGER, name TEXT);
CREATE TABLE tab2(id INTEGER PRIMARY KEY ASC,id1 INTEGER,id2 INTEGER,relationship TEXT);

Элементы в первой таблице состоят из наборов, которые имеют одинаковое значение для set поля. Я хочу дублировать любой заданный набор с новым идентификатором set, так что новый набор содержит те же элементы и отношения исходного набора. Если все элементы в наборе имеют последовательные идентификаторы, я могу сделать это следующим образом. Сначала найдите наивысший идентификатор в наборе (в этом случае, установите 3):

SELECT id FROM tab1 WHERE set=3 ORDER BY id DESC LIMIT 1

Я назначаю это переменной $ oldid. Затем я дублирую элементы в tab1, соответствующие указанному набору, предоставляя им новый набор (в данном случае 37)

INSERT INTO tab1 (set,name) SELECT 37, name FROM tab1 WHERE set=3 ORDER BY id ASC

Затем я получаю идентификатор последней строки и присваиваю ее переменной $ newid:

SELECT last_insert_rowid()

Затем я назначаю $ diff = $newid- $ oldid. Поскольку исходный набор имеет последовательные идентификаторы, я могу просто выбрать исходные отношения для set = 3, а затем добавить разницу:

INSERT INTO tab2 (id2,id2,relationship) SELECT id1+$diff,id2+$diff,type FROM tab WHERE id1 IN (SELECT id FROM tab WHERE set=3)

Но это не работает, если набор не состоит из последовательных идентификаторов в tab1. Я мог бы выполнить полный запрос исходных идентификаторов, а затем создать сопоставление 1:1 для вновь вставленных идентификаторов для набора 37, а затем добавить разницу между каждой строкой, а затем вставить вновь вычисленные строки в таблицу. Но для этого требуется загрузка всех кликов клиенту и выполнение всей работы с клиентом. Есть ли способ создать запрос, который делает это на сервере в общем случае?

1 ответ

Предполагая, что (set, name) является ключом-кандидатом для tab1, вы можете использовать эти столбцы для поиска соответствующих значений:

INSERT INTO tab2(id1, id2, relationship)
SELECT (SELECT id
 FROM tab1
 WHERE "set" = 37
 AND name = (SELECT name
 FROM tab1
 WHERE id = tab2.id1)),
 (SELECT id
 FROM tab1
 WHERE "set" = 37
 AND name = (SELECT name
 FROM tab1
 WHERE id = tab2.id2)),
 relationship
FROM tab2
WHERE id1 IN (SELECT id
 FROM tab1
 WHERE "set" = 3)
 OR id2 IN (SELECT id
 FROM tab1
 WHERE "set" = 3)

licensed under cc by-sa 3.0 with attribution.