Add UPDATE into exsisting INSERT script

Hi all

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?

$st = $mysqli->prepare("
  INSERT INTO events_test(
  DTEND
  , UID
  , DTSTAMP
  , LOCATION
  , DESCRIPTION
  , DTSTART
  , SUMMARY)
  
  VALUES (?, ?, ?, ?, ?, ?, ?)
");

// bind variables to insert query params
mysqli_stmt_bind_param($st, 'sssssss'
  , $dtend
  , $uid
  , $dtstamp
  , $location
  , $description
  , $dtstart
  , $summary);

//read json file and convert json object to php associative array
$json = json_decode(file_get_contents('events.json'), true);

// loop through the array
foreach ($json as $row) {
.....

Thanks, Barry

use the INSERT statement’s ON DUPLICATE KEY UPDATE option

3 Likes

Didn’t realise this was available :smile:

Just looking at some information online…

One question, will ON DUPLICATE KEY UPDATE update the rows even if no data has changed?

I mean I will have a big collection of older rows over time and don’t want to update every row in the db_table if no data has changed.

Thanks, Barry

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.

and update those in which it does

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

Thanks, Barry

first of all, how would you know?

okay, more seriously, the manual says when a key is duplicated, it performs an UPDATE, and the UPDATE syntax page says…

helps?

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.

first of all, how would you know?

Lots of manual labour :slight_smile:
This is why I’m trying to build an application that will do the thinking for us.

helps?

I’ll have to do some testing r937

Small example…
If I have a timestamp column for last edited of that row, and as you say…

MySQL notices this and does not update it

…then the timestamp will keep the old date and not update because nothing has changed?

I was just reading a possible solution if this is not the case using

CASE WHEN ??? <>

Cheers, Barry

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.

I’m feeding it a JSON file of data.

Though as r937 says

MySQL notices this and does not update it

I’ll only know once I do some testing… just not exactly sure how to add the ON DUPLICATE KEY UPDATE correctly to my code so I can?

Cheers, Barry

no, i meant how would you know that the update was or was not done?

if it leaves the values alone, they’ll be the same as they were before

if it updates the row with same same values, they’ll be the same as they were before

so how would you know what happened?

it was only mostly a rhetorical question

(“mostly dead is still partly alive” – miracle max)

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.

Thanks, Barry

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:

  1. I have a DB full of events populated using INSERT from JSON file
  2. Some events inside this JSON file will/might have their dates, descriptions or times changed over time
  3. 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

Barry

I have made a small change to the script and json file.

It seems to work :slight_smile:

I changed one value in the json file which updated ok using the below code, though still unsure if any other rows have been effected.

$st = $mysqli->prepare("
  INSERT INTO events_test(
  DTEND
  , UID
  , DTSTAMP
  , LOCATION
  , DESCRIPTION
  , DTSTART
  , SUMMARY)
  
  VALUES (?, ?, ?, ?, ?, ?, ?)
  
  ON DUPLICATE KEY UPDATE

  DTEND = VALUES (DTEND)
  , DTSTAMP = VALUES (DTSTAMP)
  , LOCATION = VALUES (LOCATION)
  , DESCRIPTION = VALUES (DESCRIPTION)
  , DTSTART = VALUES (DTSTART)
  , SUMMARY = VALUES (SUMMARY)

");

And wondering if this is the correct way to use ON DUPLICATE KEY UPDATE?

I’ll need to do further testing tomorrow and check if all the rows have been updated some how.

Thanks, Barry

what happened when you tested it? ™

:smiley:

1 Like

It worked ha :grinning:

Still need to do testing… I also spoken with a colleague today who suggest MERGE was the answer, though not sure about that.

Will post back once I have a final conclusion.

Thanks, Barry

Think we have it…
I have created a timestamp column as follows:

Name	: MODIFIED
Type	: TIMESTAMP
Default	: NULL
Attributes : on update CURRENT_TIMESTAMP

I then change one file in the json again, ran the script and hay presto!

All the timestamps stay blank besides the updated row I had changed :sunglasses:

MySQL notices this and does not update it

I think you are right r937… good catch!

Seems to work very well up to now, unless you can spot any issues with this, I’d say this is a production ready script?

And should I have the default as NULL?

Thanks, Barry

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.