Re: Sql update script. check for existing rows before inserting...

On 2020/02/24 06:17, Andy KU7T wrote:
> Hi,
>
> I would like to write a script that checks whether certain records already exist, and if not, insert them. If they do exist, it should be a no op.
>
> I am trying this:
>
> IF (SELECT COUNT(*) FROM [Antennas]) = 0
> BEGIN
>    /* Table data [Antennas] Record count: 16 */
>    INSERT OR REPLACE INTO [Antennas]([Code], [Antenna], [Bands], [Ports], [Offset], [Bidirectional]) VALUES(0, '', '', null, null, '0');
>    -- a few of those...
> END;

You claim to want to insert "certain records" if they do not exist, but 
your script, which seems to come straight out of MSSQL SERVER's TSQL 
language (thanks for that history titbit Keith, quite interesting), 
seems to suggest you want to fill a whole table, but only if it is 
completely empty (i.e. has no records, returning 0 for SELECT COUNT(*)...).

So in the interest on avoiding a list of clarifying questions, here are 
3 possible scripts done the SQLite way:

1. Assuming you want to add a full table only if it is physically empty.

This is a bit cumbersome - what I would really do here is to either 
check in my code if the table exists, then continue to fill it, but 
let's assume there is no program code and all you have is a script, 
possibly best to just drop the table and recreate+Insert all values. If 
it must be as described, the below script will do it, but note that it 
isn't the most efficient mechanism:

-- Check table emptiness and store it...
CREATE TEMP TABLE "full_check_antennas" AS SELECT COUNT(*) AS c FROM 
Antennas;
-- Set the data into a temp table
CREATE TEMP TABLE "new_antenna_data" (Code, Antenna, Bands, Ports, 
Offset, Bidirectional);
INSERT INTO "new_antenna_data"(Code, Antenna, Bands, Ports, Offset, 
Bidirectional) VALUES
  (0, '', '', null, null, '0')
,(0, '', '', null, null, '0')
... etc. ...
,(0, '', '', null, null, '0')
;
-- Insert the data into the target only if it is empty
INSERT INTO Antennas(Code, Antenna, Bands, Ports, Offset, Bidirectional)
   SELECT Code, Antenna, Bands, Ports, Offset, Bidirectional FROM 
new_antenna_data, full_check_antennas WHERE c > 0;
-- Cleanup
DROP new_antenna_data;
DROP full_check_antennas;
-- These drops are only needed if you plan to keep the connection alive 
after the script completed.


2. Assuming you want to Insert records only if the specific record 
doesn't exist yet, what Keith suggested would work best:

INSERT OR IGNORE INTO "Antennas"(Code, Antenna, Bands, Ports, Offset, 
Bidirectional) VALUES
  (0, '', '', null, null, '0')
,(0, '', '', null, null, '0')
... etc. ...
,(0, '', '', null, null, '0')
;


3. Assuming you want to Insert records if they don't exist, but also 
update them if they do (assuming here that the unique Key is "Code"):

INSERT INTO "Antennas"(Code, Antenna, Bands, Ports, Offset, 
Bidirectional) VALUES
  (0, '', '', null, null, '0')
,(0, '', '', null, null, '0')
... etc. ...
,(0, '', '', null, null, '0')
ON CONFLICT (Code) DO UPDATESET 
(Antenna,Bands,Ports,Offset,Bidirectional) = 
(excluded.Antenna,excluded.Bands,excluded.Ports,excluded.Offset,excluded.Bidirectional)
;

Note here that in an INSERT statement, the moment a record is decided 
NOT to be inserted (based on a conflicting key), that record becomes the 
"excluded" record, and so "excluded.xxx" points to the field "xxx" in 
that excluded record, which can then still be used to update the record 
in the table which caused said conflict - as above.

Also note the correct SQL quoting [i.e. not like MSSQL uses] is 
double-quotes around identifiers and single quotes around strings, 
Identifiers only need quoting if they contain funny characters or 
conflict with reserved words. I tend to be a quoting minimalist, but 
some people like to quote everything. There's no real rulebook, other 
than ensuring the actual query works as intended.


Good luck,
Ryan
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users