Trying to update certain column with newly created id. Example:
╔═══════════════════════════╗
║ TABLE_LIST_BINDER ║
╠════╦══════════════════════╣
║ id ║ tbl_id_lists ║
╠════╬══════════════════════╣
║ 1 ║ 1,2,3,4,5,6,7,8,9,10 ║
╚════╩══════════════════════╝
╔═════════════════════════════╗
║ TABLE_LIST_ITSELF ║
╠════╦═════════════╦══════════╣
║ id ║ text_or_sm ║ some_val ║
╠════╬═════════════╬══════════╣
║ 11 ║ ok lets see ║ ikd123 ║
╚════╩═════════════╩══════════╝
Alright so this is a rough example of what I have.
Text explanation: I'm trying to update tbl_list_binder's tbl_id_lists, by adding just freshly inserted row to TABLE_LIST_ITSLEF using postgresql. Is it doable in one query?
My current state of SQL code:
UPDATE TABLE_LIST_BINDER
SET tbl_id_lists=concat(tbl_id_lists,','(
INSERT INTO TABLE_LIST_ITSELF (text_or_sm, some_val)
VALUES ('letz danse','mkei')
RETURNING id))
WHERE id=1
My approach was: Using concat to combine, existing text of the value, then a comma and finally the returning ID value..
with _insert as
(
INSERT INTO TABLE_LIST_ITSELF (text_or_sm, some_val)
VALUES ('letz danse','mkei')
RETURNING id
)
UPDATE TABLE_LIST_BINDER
SET tbl_id_lists=concat(tbl_id_lists,',' || (select id from _insert))
where id = 1;