У меня есть две таблицы, которые ссылаются друг на друга:
CREATE TABLE Room
room_id INTEGER PRIMARY KEY,
room_name TEXT UNIQUE NOT NULL;
CREATE TABLE Item
item_id INTEGER PRIMARY KEY,
room_id INTEGER,
item_name TEXT,
FOREIGN KEY (room_id) REFERENCES Room (room_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
Теперь я хочу добавить новую комнату и добавить в нее несколько десятков предметов.
INSERT INTO Room(room_name) VALUES ('Living Room');
Скажем, я не знаю, сколько там комнат, и я просто хочу положить вещи в гостиную. Для этого мне нужно выбрать правильный room_id
. Для одного предмета это не так уж плохо:
INSERT INTO Item(room_id, item_name)
SELECT room_id, 'Couch' AS item_name FROM Room WHERE room_name = 'Living Room';
Но что, если я хочу вставить кучу значений одновременно. Я пытался использовать last_insert_rowid
, но это не рассматривает все INSERT
как одну транзакцию. Другими словами, последний идентификатор продолжает увеличиваться.
INSERT INTO Item (room_id, item_name)
VALUES
(last_insert_rowid(), 'Chair'),
(last_insert_rowid(), 'TV'),
(last_insert_rowid(), 'Carpet');
Я хотел бы избежать использования SELECT
в каждой новой строке. Есть ли способ вставить несколько значений в Item
, ссылаясь на последнее известное room_id
в Room
?
Что-то вроде CROSS JOIN
, вероятно, было бы очень полезно, но я не знаю, как заставить константы вести себя в этом случае.
Конечный результат, который я ищу, это чтобы Room
выглядел так:
room_id | room_name
--------+-----------
1 | Living Room
И Item
вот так:
item_id | room_id | item_name
--------+---------+-----------
1 | 1 | Chair
2 | 1 | TV
3 | 1 | Carpet
Вы можете использовать CROSS
объединение идентификатора, полученного из новой комнаты, с CTE, который возвращает элементы, которые вы хотите вставить:
WITH cte(item_name) AS (VALUES ('Chair'), ('TV'), ('Carpet'))
INSERT INTO Item (room_id, item_name)
SELECT r.room_id, c.item_name
FROM Room r CROSS JOIN cte c
WHERE r.room_name = 'Living Room';
Смотрите демо.
Если вы используете версию SQLite, которая не поддерживает CTE, используйте UNION ALL
в подзапросе:
INSERT INTO Item (room_id, item_name)
SELECT r.room_id, c.item_name
FROM Room r
CROSS JOIN (
SELECT 'Chair' item_name UNION ALL
SELECT 'TV' UNION ALL
SELECT 'Carpet'
) c
WHERE r.room_name = 'Living Room';
Смотрите демо.
Или просто выберите литерал. Похоже, мне нужно вернуться к учебнику.
Почему-то второе решение кажется мне более легким для чтения...
Я не знал, что вы можете сделать CTE из таких литералов с помощью
VALUES
. Это здорово. Спасибо.