Dynamic update query from within a subquery

[younanjo]

Hi guys,

For a query such a below:

-- reference: https://stackoverflow.com/a/18799497/321829
update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)
) as c(column_b, column_a)
where c.column_b = t.column_b;

How do you write a dynamic update such that the values are provided as param. I've tried below which fails:

update test as t set
    column_a = c.column_a
from (
      ${sql(data)}
) as c(column_b, column_a)
where c.column_b = t.column_b;

and below i don't get any feedback from. .describe() says nothing (but doesn't fail either).

update test as t set
    column_a = c.column_a
from (
        VALUES
        ( ${data[0].id}, ${data[0].someOtherId} )
        -- more rows go here
) as c(column_b, column_a)
where c.column_b = t.column_b;

-- where data[] is [{id: 1, someOtherId: 2},  {id: 2, someOtherId: 1}, .... ]

[porsager]

Hi @younanjo - sorry for the late reply

you should be able to do like this: (note the added values keyword before the dynamic parameter)

sql`
  update test as t set
      column_a = c.column_a
  from (values
        ${ sql(data) }
  ) as c(column_b, column_a)
  where c.column_b = t.column_b;
`

[younanjo]

@porsager does the above snippet you provided work for you? I tried it and i get a has 1 columns available but 2 columns specified error.

Code:

const data: {id: string; isSelected: boolean}[] = [{id: 'foo', isSelected: true}];

await sql`
      UPDATE test_table as ca
      SET is_selected = c.is_selected
      FROM (VALUES ${sql(data)}) AS c(id, is_selected)
      WHERE c.id = ca.id
      RETURNING *
`;

Produced SQL statement:

UPDATE test_table as ca
SET is_selected = c.is_selected
FROM (VALUES ($1)) AS c(id, is_selected)
WHERE c.id = ca.id
RETURNING *

[younanjo]

found my answer here. 👍

[porsager]

Hi @younanjo - Glad you found a way, would you mind posting what you found out? (useful for others ending up in this thread)

[younanjo]

oh sure, boils down to values helper expects an array of values for single row insert, or an array with arrays of values for multi row insert. (should probably be added somewhere in docs)

so in my case:

// Input
  const input: { id: string; isSelected: boolean }[] = [
    { id: 'foo', isSelected: true },
    { id: 'bar', isSelected: false },
  ];
  
// Transformed to array of arrays
  const data = input.map((d) => [d.id, d.isSelected]);

  await sql`
      UPDATE test_table as ca
      SET is_selected = c.is_selected
      FROM (VALUES ${sql(data as any)}) AS c(id, is_selected)
      WHERE c.id = ca.id
      RETURNING *`;
`;