Allow multiple updates

[akermabon]

First of all thank you for this great library !
I would like to be able to build the following kind of query.
I think it isn't possible at the moment or maybe I just could not find how.

        update trad.bcas as t
        set quantity = v.quantity::integer
        from (values
            ($1, $2),
            ($3, $4),
            ($5, $6)
        ) as v(code, quantity)
        where t.store_id = $7
        and t.schedule_id = $8
        and t.product_code = v.code
        returning *

I did it using the following, code

    const bcas = [{
        quantity: 1,
        code: '2310'
    }, {
        quantity: 2,
        code: '2730'
    }, {
        quantity: 3,
        code: '3511'
    }];
    
    let qb = sq.l`
        update trad.bcas
        set quantity = v.quantity::integer
        from (values
    `;

    const last = bcas.length - 1;
    bcas.map((b, index) => {
        qb = qb.l`(${ b.code}, ${ b.quantity })`;
        if (index !== last) qb = qb.l`,`;
    });

    qb = qb.l`) as v(code, quantity)
        where store_id = ${ storeId }
        and schedule_id = ${ scheduleId }
        and product_code = v.code
    `;

is there a better way atm ?
Thanks :)

[eejdoowad]

You're right. Right now there is no better way to write it because there's no mechanism for adding an additional from clause to an update query.

According to Postgres's grammar, there can only be a single update table. I think I'll change how update queries are compiled so that the first table passed to .from is used as the update table, and subsequent tables are used in the from clause.

I've also been thinking about adding an overload to .from that accepts an object, and this seems like the perfect use case. Object keys will be table names (to the right of as), and object values will the source tables (either names or an array of values).

You'd then be able to write your query as:

const bcas = [{
  quantity: 1,
  code: '2310'
}, {
  quantity: 2,
  code: '2730'
}, {
  quantity: 3,
  code: '3511'
}];

sq.from({
    t: 'trad.bcas',
    v: bcas
  })
  .set`quantity = v.quantity::integer`
  .where({
    't.storeId': storeId,
    't.scheduleId': scheduleId,
     equalProductCode: sq.l`t.product_code = v.code`
  })
  .return`*`

[akermabon]

That would be great ! Feel free to use my case for your needs.
I'd be glad to try out some wip if you want me to.
Thank you for your quick answer :)

[eejdoowad]

This is now possible using sqorn-pg v0.0.27. Your query can be written as:

sq.from({ t: 'trad.bcas' }) // first .from call used for UPDATE CLAUSE
  .from({ v: bcas }) // subsequent .from calls used for FROM CLAUSE
  .set`quantity = v.quantity::integer`
  .where({
    't.storeId': storeId,
    't.scheduleId': scheduleId,
    join: sq.l`t.product_code = v.code`
  })
  .return`*`

or as

const values = sq .extend(...bcas.map(b => sq.l`(${b.quantity}, ${b.code})`)).join(', ')

sq.l`update trad.bcas as t`.l`set quantity = v.quantity::integer`
  .l`from (values ${values}) as v(quantity, code)`
  .l`where (t.store_id = ${storeId} and t.schedule_id = ${scheduleId} and t.product_code = v.code)`
  .l`returning *`

Thanks for the feedback. Let me know if you have any other suggestions.