How to understand where the error is in a SQL query that fails with this message Syntax error near 'INSERT INTO `

Andrea D_ 0
Viewed 45

I need to run a SQL query that inserts some stuff in the table if the table has not a record with the name "Orders". So far I did this:

IF NOT EXISTS (
  SELECT 
    *
  FROM
    gridconfigs
  WHERE
    gridconfigs.name = 'Orders'
)
INSERT INTO
  `gridconfigs` (
    `ownerId`,
    `classId`,
    `name`,
    `searchType`,
    `type`,
    `config`,
    `description`,
    `creationDate`,
    `modificationDate`,
    `shareGlobally`
  )
VALUES(
....

And this is the error I am getting:

Errore nella query (1064): Syntax error near 'INSERT INTO `gridconfigs` ( `ownerId`, `classId`, `name`, ...' at line 9

I do not get what the arror actually is.

mariadb sql
1 Answers

You can write a SELECT with the EXISTIS, instead of a INSERT INTO VALUES

CREATE TABLE   `gridconfigs` (
    `ownerId` int,
    `classId` int,
    `name` varchar(19),
    `searchType` int,
    `type` int,
    `config` int,
    `description` int,
    `creationDate` int,
    `modificationDate` int,
    `shareGlobally` int
  )
;
    



INSERT INTO gridconfigs
    
VALUES
(1,2,'Orders',4,5,6,7,8,9,10)
;


INSERT INTO
  `gridconfigs` (
    `ownerId`,
    `classId`,
    `name`,
    `searchType`,
    `type`,
    `config`,
    `description`,
    `creationDate`,
    `modificationDate`,
    `shareGlobally`
  )
    SELECT 
    1,2,3,4,5,6,7,8,9,10
    WHERE NOT EXISTS ( SELECT 1 FROM gridconfigs  WHERE  gridconfigs.name = 'Orders')

Records: 0  Duplicates: 0  Warnings: 0

fiddle

nbk0
Related