Upsert table contents into MYSQL database

[Sergio]

Hi Community!

I am trying to wrap my head around how to use the table contents as the filter to update records or create items on the unique_strings table, this table has its origin in the query JSON with SQL resource that I created.

I am a total noob on this but want to give it a shot to a complicated CRUD using retools.

Thanks for any orientation.

[Tess]

Hi @Sergio,

Thanks for your question!

I have a few follow up questions --

1. Do you want to do bulk upserts or upsert individual rows? I am asking because your upsert query currently has the action type for 1 upsert, but we do have an action type for bulk upserts too

2. I see that query11 references table2 and the upsert query references table3, are you looking to select a row from table2, and then display that selected row in table3 where the user can make changes or add new rows?

Generally speaking, here's an example that may help! Also, our docs for tables are helpful https://docs.retool.com/docs/data-in-tables#editable-table-columns

In the screenshots, I have a database table on the left (table1), and a table on the right (table2) that is populated by a Query JSON with SQL query that reflects the selectedRows of table1.

Then, I have table2 set to allow the name & job columns to be editable & I've toggled on the "Show add row button" setting.

Next, I have a database query that is doing a bulk update. I cannot reference selectedRow here because the .selectedRow property doesn't have access to the pending updates that I have made (i.e. changing the second row's name in table2 to "Sarah"). Instead, I need to reference table2.recordUpdates, which stores these edits. Since were doing an upsert, I also want to accept new rows. For this reason, I also want to include table2.newRow, which has that new data I want to add (i.e. name=Tess, job=Support Engineer)

When I run the upsert query & then re-run my original database get query, I see that my bulk upserted data is reflected:

[Sergio]

@Tess

Do you want to do bulk upserts or upsert individual rows? I am asking because your upsert query currently has the action type for 1 upsert, but we do have an action type for bulk upserts too

Thank you @Tess !

Answering your questions:

1. Do you want to do bulk upserts or upsert individual rows? I am asking because your upsert query currently has the action type for 1 upsert, but we do have an action type for bulk upserts too

I want to bulk upsert but I know that in my file there will be repeated addresses so I don't know how to go around the duplicate primary key error

2. I see that query11 references table2 and the upsert query references table3, are you looking to select a row from table2, and then display that selected row in table3 where the user can make changes or add new rows?

Sorry about this part, I am trying to arrange the columns so I can make the bulk insert, this table really only has that function.
I really don't want the users making individual changes, I am using selectable rows because I understand is the only way I can do bulk inserts from these tables at least on the GUI.

Thanks for your help, I checked the document and it has a lot of info but still can figure it out

[Tess]

Hi @Sergio

Got it! Do you have a different column that could be a primary key, or would you be able to add one? You can hide it from view in the table, if needed.