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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Andy KU7T >Sent: Sunday, 23 February, 2020 21:17 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >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
