-1

Being a DB noob, I'm trying to generate SQLite statements to import records with "First_Reported" and "Last_Reported" datestamps, but can't seem to get the "First_Reported" working correctly.

I have a few years worth of scans I'm trying to import. There'll be plenty of duplicates, so I just want to update the "Last_Reported" column if the record already exists. If not, then create the record.

I've searched SO and have tried following some of the other solutions (SQLite - UPSERT *not* INSERT or REPLACE), I just can't seem to get this right. I'm not sure if I'm going about this the wrong way, or just fat fingered something?

Any suggestions? For hundreds of thousands of rows, should I be using something else? I'm looking for efficiency and don't want something that "just works". I want to do it the "right" way.

I created the table as such;

CREATE TABLE Scan_Results (First_Reported text, Last_Reported text, Hostname text, IPAddress text, Network text, OSInformation text, DisplayPath text, TestName text, Message text, FindingObject text, FindingResult text, FindingExpr text, UNIQUE (Hostname, IPAddress, Network, OSInformation, DisplayPath, TestName, Message, FindingObject, FindingResult, FindingExpr));

And I'm trying to insert/update the rows with this;

WITH new (First_Reported, Last_Reported, Hostname, IPAddress, Network, OSInformation, DisplayPath, TestName, Message, FindingObject, FindingResult, FindingExpr) AS ( VALUES('2017-08-15', '2017-08-30', 'someHost', 'someIP', 'someNet', 'someOS', 'someDisplay', 'someTest', 'someMessage', 'someObject', 'someResult', 'someExpr') )
INSERT OR REPLACE INTO Scan_Results (First_Reported, Last_Reported, Hostname, IPAddress, Network, OSInformation, DisplayPath, TestName, Message, FindingObject, FindingResult, FindingExpr)
SELECT old.First_Reported, new.Last_Reported, new.Hostname, new.IPAddress, new.Network, new.OSInformation, new.DisplayPath, new.TestName, new.Message, new.FindingObject, new.FindingResult, new.FindingExpr
FROM new LEFT JOIN Scan_Results AS old ON new.Hostname = old.Hostname;
Nathan
  • 19
  • 3

1 Answers1

0

I seem to have figured it out (maybe?) by formatting it differently. Made it a little easier for a noob like me to read. It's also from one of the answers I tried before. My other question still stands. Is this the "proper" way of dealing with hundreds of thousands of records? Or is there a more efficient way of doing this?

REPLACE INTO Scan_Results (
  First_Reported,
  Last_Reported,
  Hostname,
  IPAddress,
  Network,
  OSInformation,
  DisplayPath,
  TestName,
  Message,
  FindingObject,
  FindingResult,
  FindingExpr)
SELECT
  IFNULL(old.First_Reported,"05/01/2015"),
  "12/01/2017",
  "someHost",
  "someIP",
  "someNetwork",
  "someOS",
  "someMessage",
  "someObject",
  "someMessage",
  "someObject",
  "someResult",
  "someExpr"
FROM Scan_Results 
LEFT JOIN Scan_Results AS old ON old.Hostname = "someHost";
Nathan
  • 19
  • 3