I am going to export a table, but the problem is there may be duplicate primary keys. I want to update those records.
I checked the community, and I found couple of articles. Basically the simple way is, I need to run a query to delete the records that have duplicate keys in the target table first.
In easy morph, there is a place for me to write a query that will be executed before I run the export step. How ever, how do I reference the existing table to get the duplicate records list?
For example, I need to import a table Table1 from spreadsheet and export to somewhere with exactly same structure. Id is the primary key. Step 1, I import a table from spread sheet. (This is the first confusion thing here, I don’t know the name of the table after imported!)
Step 2, export. I can write my query like so
Delete From TargetTable Where Id In (Select Distinct(Id) From (??? what is the name of my imported table???)); Then export to TargetTable now, so I don’t have any duplicated primary key any more.
Thanks Andrew, but that wouldn’t work, as I need to delete the target table with the matching key, not the source table. Please read my question, the problem is when I try to delete the target table, I do not know how to refer back the matching key as they are in Easy Morph, but the query that I am trying to run is on the target database.
A spreadsheet is not automatically imported as a database table. EasyMorph's "Import Excel spreadsheet" action will import spreadsheet data to EasyMorph as a dataset and store that dataset in memory. Then you have to use one of the DB-related action to modify the target table according to the imported data in one way or another.
That is exactly what "Delete matching database rows" action does. It deletes rows in the target table when values in that rows match values in the current EasyMorph dataset.
It will update rows with IDs 1, 3 and 5 with values, loaded from a spreadsheet file.
"Import spreadsheet" and "Update rows in the target database table" tables is your actual workflow.
"Create target database table" create a table, used by this example and the other two tables just display "before" and "after" state of the target table.
The target database table is created with UNIQUE constraint on the ID column instead of PRIMARY KEY, but those constraints are the same from the perspective of the current example.
Step 2 in ‘Update rows in the target database table’ is the step that I was looking for, and I didn’t find it until I see your example (In the past I thought it is referring to the query that run before export).
