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

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <table> (<column list>) VALUES (<value list>);

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 <[hidden email]> On
>Behalf Of Andy KU7T
>Sent: Sunday, 23 February, 2020 21:17
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
In reply to this post by Andy KU7T
SQLite is not a procedural language. IF is not a programming construct, it is part of an expression.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Andy KU7T
Gesendet: Montag, 24. Februar 2020 05:17
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

R Smith-2
In reply to this post by Andy KU7T
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

R Smith-2
In reply to this post by Andy KU7T
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users