How to use composite primary key for bulk updates?

Composite Primary Keys - https://www.javatpoint.com/sql-composite-key

My tables are designed to use the composite primary key for relational/join tables.

I want to perform bulk updates on those tables and bulk update via a primary key allow to select only one field from a table as a primary key.

Any way I can implement this?

Hey @jignesh - bulk update queries are on a table by table basis - do your individual tables not have primary keys at all without joining to other tables and creating composite primary keys?

Hey @justin, thanks for the response.

Consider the below example as a table and I want to update entries on this table only.

table name: table_xy_join
fields: tablex_id, tabley_id, value
primary key(tablex_id, tabley_id)

So, normally my query is something like this.

update table_xy_join
set value = ‘new value’
where tablex_id = x_id and tabley_id = y_id

hope this gives enough idea about the things I am looking for.

Thanks @jignesh! Yep, unfortunately our bulk update option only works with a primary key that’s one of the columns of the table that you’re trying to update. So unless you have a column in table_xy_join that represents the primary key, it’s not going to work. Do you think you’d be able to add in a placeholder column for that compound key or something like that?

Thank @justin.
Any way you suggest how can we make those new column data sync with composite key?

@jignesh nothing off the top of my head right now, unfortunately. I’ll ask around!

Good news @jignesh - @alex had an idea that might work. Our GUI for SQL bulk updates is limited to a single primary key, but you can also just execute arbitrary SQL against your database as well via our editor. If you connect your resource as a “read” resource (you’ll see the two options when you connect a data source), you should be able to run UPDATE statements manually in the editor. Would that work?

Thanks @justin & @alex.
I think this work-around looks good to me.

Thanks much for your support :+1:

1 Like