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

[R.Smith]

Send-before-checking failure. :)

Corrections to my previous mail:

1. "... is to check in my code if the table exists" must read: "... is 
to check in my code if the table is empty"


2. "...ON CONFLICT DO UPDATESET (Antenna..." must read: "...ON CONFLICT 
DO UPDATE  SET (Antenna..."



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R.Smith]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

IF is not an SQL statement.

IF is a part of your host application programming language.  It may also be 
part of a proprietary vendor specific extension to the SQL language to permit 
programmability such as the Sybase TRANSACT-SQL (licensed to Microsoft as 
Microsoft SQL Server to run on Microsoft OS/2, since IBM already had a 
relational database called IBM DB2 that ran on OS/2 and Microsoft needed 
something too (to keep up with the Jonses).  Laster, when Microsoft OS/2 2.0 
New Technology got renamed Windows NT, Microsoft still needed a database server 
for it, they "made an arrangement with Sybase to take over the defunct version 
of Sybase SQL Server and call it Microsoft SQL Server, while Sybase agreed to 
"stay away from" using the old deprecated version and leave that code line to 
Microsoft.  How many Billions of Dollars Microsoft payed Sybase for this 
purchase is undisclosed) or ORACLE PL/SQL.

Some SQL database engines have proprietary procedural extensions to SQL.  
SQLite is not one of them.

The way to insert a record if it does not exist or ignore the fact that it does 
exist is to use the IGNORE conflict resolution method.  In order for this to 
work the table must have a declared unique key by which a "duplicate" can be 
detected.

The syntax is:

INSERT OR IGNORE INTO  () VALUES ();

This will cause errors (such as unique key violations) to cause the insert 
statement to be ignored.  Contrast this with your INSERT OR REPLACE which 
deletes conflicting records then performs the insert, which is an entirely and 
completely different thing altogether.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Andy KU7T
>Sent: Sunday, 23 February, 2020 21:17
>To: SQLite mailing list 
>Subject: [sqlite] Sql update script. check for existing rows before
>inserting...
>
>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;
>
>However, I am getting a syntax error near IF. Any ideas where my error
>is?
>
>Thanks
>Andy
>
>Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for
>Windows 10
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sql update script. check for existing rows before inserting...

[Andy KU7T]

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;

However, I am getting a syntax error near IF. Any ideas where my error is?

Thanks
Andy

Sent from Mail for Windows 10

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users