Update tabls joined with a temporary table

Data Service
1

What’s the best practice to update joined table using backendless Database API?

This is my need:

I imported data from csv to a temporary table
I want update application’s tables joining them with temp table and, in details:

  1. update existing records (records found in both table)
  2. add new record (records in temp table that aren’t in application’s tables)
  3. delete old record (records in application’s table that aren’t in temp table)

I’m using coding javascript api service.

Backendless version 6.4.4
SDK for Javascript

2

Hello @Elena_Aralla ,
Welcome to our community and thank you for trying out Backendless.

We do not support Inner Join. You can search the two tables with the conditions you need, after which you can perform the actions you need.

Regards, Nazar

3

hello @Elena_Aralla

you can join the temp table with the application table by relations. Create a relation column in the application’s table, which points to the temp table. Then join records in tables using API Set Relation using condition - Backendless SDK for JavaScript API Documentation go through all objects in application’s table and set the relation for it.

  1. To update existing records in both tables you can use transactions Updating multiple objects - Backendless SDK for JavaScript API Documentation . So you can create unit of work with two operations, one to update the temp table and another to update the application’s table

  2. Find all records that have no relation to the application’s table

  3. delete all records that have no relation to temp

4

Thank you @sergey.kuk !
I’ll try with your suggestion.
have a nice day,
elena.

5

Hi @sergey.kuk,

not sure to undestand the first part of your response;
I have the situation explained in this image:

Schermata 2022-09-09 alle 12.05.45
Schermata 2022-09-09 alle 12.05.452558×1454 264 KB

with this data, for example, imported on the temp table.

Schermata 2022-09-09 alle 11.53.24
Schermata 2022-09-09 alle 11.53.241508×832 162 KB

I need to import Customer avoiding duplication; saying that Customer on tmp table is identified by ID, and there are two different names FOR THE SAME ID, I need to select only the first one.

After imported Customer, I need to import Plans (avoiding duplication) generating the relationship between Customer and Plan

I don’t understand how adding join between application’s table and temp table can help.

Thanks,
Elena.

6

Hi Elena,

To understand how to structure the CSVs with related data, I recommend adding a few sample records in each table, establishing relations between them and then running an export of your data on the Manage > Export screen of the app. On the Export screen make sure to select the entire “closure” of the tables. The resulting zip file with all the CSVs will be placed into the /export directory in the file storage of your app. It will be the best representation of the format you should use to import related data.

Regards,
Mark

7

Hi @mark-piller,

at the moment I’ve a Springboard plan…

export not available :frowning:

8

I just posted a cookbook article describing how to format relational data in CSVs:

Hope it helps.

Regards,
Mark

9

Hi @mark-piller,
Thank you for the cookbook!

Is there a way to call backendkess’s import process from an Api Service?

Let me explain: in my application end user will upload a CSV with all data; at the end of the upload, the application Will call an Api Service to import data in Tables. End user knows nothing of CSV formatting. He periodically will import CSV file on application to update data.