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?

1 Like

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?

1 Like

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

Thanks much for your support :+1:

1 Like

Thanks! This is a good workaround for now, but it seems a little weird to be able to modify a “read” resource :sweat_smile:

2 Likes

@justin @alex
Can you please help me with how to do bulk updates from the table’s ‘recordUpdates’ array and only with changed data query using this manual update option?

@nacho heheh it’s really less of “read” and more of “no GUI” - we’re updating copy slowly

1 Like

Hey @jignesh! So the .recordUpdates property contains an entry for every column in your table - if any values have been updated (i.e. are editable in the table), those new values will appear in the property - if values haven’t been updated, then those non-updated values will appear. For a quick example, let’s say I have a table with 3 columns, and I update two of them:

I’ve only updated the first two columns (I changed the customer ID to 645, and the store ID to 21) - the email column has remained untouched. Here’s what table1.recordUpdates will evaluate to:

Screen Shot 2020-07-22 at 9.52.06 AM

You’ll notice that even though we haven’t updated the email column, it still appears - we do this to make it easier to issue UPDATE statements to your database / API. So if you wanted to issue an update query with this new data without using our SQL GUI, you could write something like this (depends on your SQL dialect, of course):

UPDATE your_table
SET customer_id = {{ table1.recordUpdates[0].customer_id }},
        store_id = {{ table1.recordUpdates[0].store_id }},
        email = {{ table1.recordUpdates[0].email }}
WHERE composite_key = whatever

You’ll notice that the email hasn’t changed, but we’re still including it here because it makes it easy to just have one update query that can handle any changes to your table (this query would still work if we only updated one column, or two, or three).

Note that if you update multiple rows then you can reference those updates via indexing .recordUpdates at [1], [2], etc.

Hopefully this helps!

Thanks, @justin for the quick response.
Yes, I completely understand these till now - index-based records updates using a single row update query.

update multiple rows then you can reference those updates via indexing - I didn’t get this part yet how can do these in retool - need some another top-level JS-based query to loop through? any reference?

Hi @jignesh!
Did you ever figure out how to loop through the records to do a SQL query per each record?

I’ve worked around this by creating a unique column which is a concatenation of the other columns, and you can use that as the primary key for the update

@jignesh @achempak @byron - there are basically two separate things to address here:

  1. Updating multiple rows with a bulk update query (using any data source: SQL, MongoDB, etc.) - to do this, you’ll need to create a query to update one row, and then use a JS query to apply that query to every updated row. I made a video for how to do that in Firebase here, but the logic applies to SQL as well: loom.com/share/91d5c6c566394731b5bd7b17fbf9b626

  2. Dealing with composite primary keys in SQL - discussed above in the thread

Hopefully this helps!

1 Like