Feature request: UPDATE from flat file

[jmfederico]

This is an extension to #224

I usually update tables using flat files (CSV), Right now one can import and insert new data, but can not UPDATE existing rows.

It would be very useful if one could use a CSF file and UPDATE rows instead of INSERTING new ones.

Thanks

[huyphams]

Thanks but could you suggest me a client has this feature? I wanna take a look.

[jmfederico]

SequelPro does it:

[huyphams]

Thank you so much 👍

[jmfederico]

The way it works on SequelPro is that you have to first select which column or columns to do the matching to, and then you select which columns to update.

See this image:

[huyphams]

I got it, thank you. I will add this support (include REPLACE) in the near future update. No worries.

[j-moltz]

Would love this feature to be added. Any update on time frame or milestone? Thank you.

[rbridge]

Would love to see this feature get added.

[rbridge]

@huyphams Any word where this feature falls the current development for TablePlus? Is this still something in the backlog?

[VampDuc]

Is there any update on this feature?

It was incredibly handy for doing simple updates from outside sources quickly.

[alecdibble]

Would love to see this feature.

[mtsteven]

Any update on this feature @huyphams ? It's the only reason I still have Sequel pro on my computer ;-)

[robertosanval]

+1 to this feature, is a must

[peterthomson]

Any update on this? Sequel Pro is aging rapidly but it's still the best CSV to SQL import / update tool. This feels like a sensible feature for TablePlus.

[Dontorpedo]

i tought importing does also update.. but it seems not, so this feature would be a must

[thoughtspacewebsites]

Just adding a voice to say that this is one of my most used features in my desktop DB client. Importing from a CSV using update from a GUI is so much faster and easier than trying to craft the SQL statement, especially when it comes to tables with large schema / many data points. This feature alone would sway me to buy a license, but in the meantime, I'm stuck using Sequel Pro in a VM.

[cdolan92]

Ditto this would be a great feature and one I miss from Sequel Pro

[fissioner]

Please code Gods, hear our plea 🙏

[huyphams]

Hi guys, if you have a chance, please download this hot patch it supports Replace/Update from CSV now. https://www.dropbox.com/s/kjeon87wr82g2ps/TablePlus.dmg?dl=0

[cdolan92]

Any idea when this might hit the main release/update in the product?

[huyphams]

We're going to release it in the next update

[peterthomson]

Ok, so the new "Update on duplicates" fundamentally works. Well done!! The mini test was to export a table as a csv. Make an arbitrary change to a data-point. Re-import the csv using 'update on duplicate' and to see what changed. One thing to clarify in the UI is that the "Update on Duplicates" uses the table's default UID as the lookup-match. This is a good start, but to reach parity with Sequel Pro, the UID used for lookup/matching needs to be user selectable.

[huyphams]

It was a bit different between TablePlus and Sequel Pro.

1. When you select update in Sequel Pro, it generates the

   UPDATE table_name SET `field` = 'value' WHERE `unique_key` = value;

=> It will update all existing rows and skip all new rows in CSV.

2. On the other hand, TablePlus handle the update only it is duplicated with the existing rows. (by primary key or unique key), it generates

INSERT INTO table ( field_ list ) VALUES
(values) ON DUPLICATE KEY UPDATE update values.

=> It will update all existing rows and insert the new rows.

We're planing to implement "REPLACE" along with "REPLACE ON DUPLICATE" feature in the future releases so the user will have more choices.

[becscarter131]

Can you confirm when the next release will be? Have been waiting for this one 🙌

[684efs3]

@huyphams Where do you put the matching criteria to determine whether a row is a duplicate?

[684efs3]

@huyphams Does this upsert or simply updates? If an item is not a duplicate, will it insert it?

[tomcoonen]

This is still missing for me regarding this functionality:

• Select fields to match on (where)
• Add static or SQL value to update the column with (update)

[dannolan]

Please I need this my family is dying

[oncul]

I can buy this app if you would add this feature. OMG It's been 6 years

[mo-rocks]

Paid subscription here, still need this improved. I should be able to "update" by importing just an ID and an arbitrary field, with the updated value in the CSV.

Unfortunately, this doesn't work - TablePlus is trying to update every single field, which is obnoxious.

[huyphams]

Hi @mo-rocks have you tried to change the field to do not import?

[huyphams]

[mo-rocks]

The field isn't present in the CSV, so it's only matching 4 out of 29 columns. I can't select "do not import" if it's not in the CSV.

The default should be that if column isn't present in the CSV, it doesn't import it

[huyphams]

I don't understand. the point of the header is that you can customize the matching between CSV columns and table columns. If you do not want to add the column to updates, just choose do not import.

[mo-rocks]

I have an items table with 29 columns. I run an SQL command to filter out some items that need updating. EG, "select id, description from items where ...". Save as CSV, update just the description. Now I have a CSV with ID and description fields, with correct ID's and updated descriptions.

In tablePlus, go to file->import, match columns by name, and it matches the ID and description fields. Try to run import, and it fails because some other fields in the items table don't have a default value. I wasn't trying to update any other fields, I only want it to match the ID and update the description, leave all the other fields untouched.

I shouldn't have to export all 29 fields to a CSV and re-upload the CSV with all 29 fields again, that's a nuisance.

[mo-rocks]

...I just tried exporting the full table header, and re-importing with only ID an description, with all other fields set to 'Do not import', and still getting the same error on no default value.

[huyphams]

Then you can just select the ID (as primary) and the descriptions. Other fields are do not import.

I just found quote bug in this feature (PostgreSQL Only), which will be fixed in the next update.

Does the ID are primary? if not I don't think it will work.

[huyphams]

Did you change to UPDATE ON DUPLICATE @mo-rocks ?

[mo-rocks]

Yes, ID is primary. Yes, it was "update on duplicate". Just for fun, I tried "replace" as well - still doesn't work. The point is, the field it's complaining about is set to "do not import" - so regardless of update or replace, it's not ignoring the field.

The field it's complaining about in my case is also part of an index - but since I'm not updating that field, it shouldn't matter. The only field I'm updating is the description.

[huyphams]

It could be a bug as should ignore the field with do not import

[mo-rocks]

OK, appreciate your prompt response. Shout back if you are able to find/fix it.

[huyphams]

Hi @mo-rocks I'm using MySQL and choose Update On Duplicate The fields with do not import are not included in the update statement. Could you confirm this? The CSV and the table have many column but I only match ID with Command other fields are do not import and I have this in the log.

[huyphams]

You can see that there is only commend and id are included in the statement.