Update Table from CSV

#1 · 2022-03-31, 4:02 pm

Hi,

I need to do a simple update to RM table (Event Table). the only column that needs to be updated is "DATE" column. The file is about 600 rows, contains two cols (EventID, Date). The EventTable has over 30,000 rows. I have done this on my mySql database website. I am using Db Browser Sqlite. Not sure how to "insert" or load file and if I would need to update from a temp table. Is there an example someone can point me to?

thanks, Kevin

#2 · 2022-04-01, 12:35 pm

If your SQLite tool supports importing from CSV, import it into a temp table and update the EventTable from it.

If not, use your spreadsheet to generate an UPDATE command for each row then copy them into SQLite and execute.

Those are the two tactics I can think of. Do you need help on the SQL statements?

#3 · 2022-04-01, 2:27 pm

The part I was stuck on was import csv /Excel to temp table and I didn't know how to make sure the field types were set same so that did not cause any issue(s). I should be okay with Sql to update (temp) table to (RM) table

thanks, Kevin

#4 · 2022-04-02, 9:22 am

Here is the SQL did on test db - did not work as desired.

There are about 600 rows in temp table and 31,000 in receiving table. I only want to update the matching EventIDs in both (so 600 events should be updated). What am I doing wrong?

Original

UPDATE EventTable
SET Date = (SELECT Date
FROM tempBurialsUpdate
WHERE tempBurialsUpdate.EventID = EventTable.EventID )

revised (is this better?)

UPDATE EventTable
SET Date = Date
WHERE EventID LIKE
(SELECT EventID FROM tempBurialsUpdate);

#5 · 2022-04-13, 11:51 am

With the caveat that I am basically clueless about SQL, here's the script that I use with SQLiteSpy to update tables from CSV files. Not sure if / how it will translate to DB Browser. I last used it to update citation names but have reused it many times, just changing the table and field names as needed.

I believe it's copied from one of Tom Holden's scripts with a little help from Google for the csv part. I generally build the csv file with 2 fields - a key value and the field I want to modify. These values automatically get assigned C0, C1 , C2, etc variable names during the import process because there are no explicit column headers defined in the file.

Last thing I wanted to add was that Tom posted good information a while back related to calculating date / time fields. Sadly, I cannot find it right now.

--

-- "Update CitationName field from file.sql"

--

-- Import csv file 'NewCitName.csv' into temp table ModCitationNameField

-- CSV file has 2 columns - CitationID,CitationName - and no headers.

--

DROP TABLE IF EXISTS ModCitationNameField

;

CREATE VIRTUAL TABLE temp.ModCitationNameField USING csv(filename='C:\Users\Admin\Desktop\RootsMagic\SQL-RM8\NewCitName.csv');

--use new values to update the CitationTable

UPDATE CitationTable

SET CitationName = (SELECT TRIM(c1)

FROM ModCitationNameField

WHERE CitationTable.CitationID=c0)

WHERE

EXISTS (

SELECT *

FROM ModCitationNameField

WHERE CitationTable.CitationID=c0

)

;

Uploaded files:

Update-CitationName-field-from-file.sql

#6 · 2022-04-14, 5:18 pm

You can find info about date/time calculations under the #date tag

Tom

#7 · 2022-04-14, 8:07 pm

Thanks Tom. Here's the specific post that I was referring to re: updating the UTCMODDate field. I looked through those tags yesterday until I was bleary eyed though I now see that the sort is by creation date not last update, so I didn't dig deep enough. Thanks again.