I recently built an INSERT script (shown below) which generally updates the DB from a JSON file, works good.
Because all records have a UNIQUE column UID which stays the same the events do not update if any of the information has changed.
My question
How do I add UPDATE into the INSERT below so if any columns/rows have been changed inside the JSON file, they UPDATE while ignoring the ones that haven’t?
And can this be done inside one query or do we need to create a separate query?
It will insert rows where it does not find the key, and update those in which it does. It won’t touch rows that don’t match a key you supply with the query.
This is what I mean… So if I have 10k of old data which is still present in the DB and JSON file on INSERT these files will update regardless if they have been changed?
Heavy processing power on the system, no?
I only want to update if anything has changed.
Example: LOCATION is changed, maybe the time DTSTART
It will only attempt to update something if you supply that key in the new data you are feeding it. If you are feeding it all the keys, I’m not sure what else you could expect from it, unless you want a ton of rows with duplicate keys for whatever reason. But yes if the data remains the same, SQL will not update it.
Well, if the timestamp is different then thats another story, it is indeed a difference in the row, but I’m not sure how to get around that with SQL. It depends on what you feed it.
no, i meant how would you know that the update was or was not done?
I could just change one row inside the json file and see if the file had changed - I’m using mysql DB and checking through my cpanel, though the json file is from a third party and unpredictable of changes. But as mention the UID will never change.
if it leaves the values alone, they’ll be the same as they were before
Correct, though if updated with no changes everything would look the same but with the overhead of replacing the exact same rows.
if it updates the row with same same values, they’ll be the same as they were before
As above.
so how would you know what happened
I’ll check why cpanel and see if the timestamp has been updated or any times and descriptions I change.
At the moment I’m still in testing so gives me more options for testing things.
Let me clarify something: Do you have a timestamp column in your SQL table? If you do, it will update all of your rows if the timestamp changes. If not, it will ignore the whole row, since the timestamp, being a column, would mean that the row would indeed be different.
Let me clarify something: Do you have a timestamp column in your SQL table?
Not at present, I was going to add one so we can test things further and see when and if the rows are updating, and how many. Though again, I can check this inside my cpanel for quick feedback to see if things work.
I have a timestamp on DTSTAMP mainly due to formatting issues which would not work in this instance.
Quick recap:
I have a DB full of events populated using INSERT from JSON file
Some events inside this JSON file will/might have their dates, descriptions or times changed over time
When I run the INSERT script, say once a week I want to only update the rows that have changed, else ignore them and move on and don’t replace any identical rows resulting in unnecessary processing