Search code examples
phppostgresqlphp-pgsql

UPDATE with INSERT RETURNING value PGSQL


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..


Solution

  • 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;