Trying to execute SQL commands only if value doesn't exists in a column

Question

I'm trying to execute SQL commands in case value doesn't exist in a column,

What this script actually trying to achieve is to insert into the t_bridge table a new set of values.

I saw this answer but I get this error - if not valid in this position.

it looks like I'm in the wrong direction here, and I'm missing something pretty crucial.

IF EXISTS (SELECT * FROM t_bridge WHERE serial_number = 1234)
BEGIN
    -- don't do anyting
END
ELSE
BEGIN
    insert into t_bridge 
    values (
           ID
          ,app_id
          ,1 
          ,'SAN-2-H-5G-EU V4' 
          ,470  /
          ,29569 
          ,35 
          ,HARDWARE_VERSION
          ,SOFTWARE_VERSION
          ,'MEN4' 
          ,DESCRIPTION
          ,DETAILS
          ,1234 
          ,STATUS
          ,INSTALL_DATE
          ,LAST_CONNECTED_DATE
          ,STATUS_CHANGED_DATE
          ,ERASED
          ,999 
          ,LOCATION_ID
          ,unix_timestamp(now())*1000  
          ,unix_timestamp(now())*1000 
          ,NULL 
          ,NULL 
          ,SITE_ID
          ,CONNECTION_TYPE
          ,NETWORK_NAME
          ,2 
          ,ICCID
          ,DATA_PLAN_VALID_DATE
          ,1 
          ,NULL
          ,'automatically'
          );

    insert into t_chap_token (BRIDGE_ID,BRIDGE_SERIAL_NUMBER,TOKEN)
     VALUES (
     (select id from t_bridge where SERIAL_NUMBER = 1234)
     ,'1234'                                                
     ,'XXXXXXXXXXX='                        
END

score 0 · Accepted Answer · answered Feb 19 '20 at 08:58

So since I'm not too familiar with SQL scripts, It turns out I needed to create and run it inside of a procedure.

So this is the way to do it:

DROP procedure IF EXISTS `PROCEDURE_NAME`;

DELIMITER $$
USE `DB_NAME`$$
CREATE PROCEDURE `PROCEDURE_NAME`()
BEGIN
     IF EXISTS (SELECT * FROM t_bridge WHERE serial_number = 1234)
BEGIN
    -- don't do anyting
END
ELSE
BEGIN
    insert into t_bridge 
    values (
           ID
          ,app_id
          ,1 
          ,'SAN-2-H-5G-EU V4' 
          ,470  /
          ,29569 
          ,35 
          ,HARDWARE_VERSION
          ,SOFTWARE_VERSION
          ,'MEN4' 
          ,DESCRIPTION
          ,DETAILS
          ,1234 
          ,STATUS
          ,INSTALL_DATE
          ,LAST_CONNECTED_DATE
          ,STATUS_CHANGED_DATE
          ,ERASED
          ,999 
          ,LOCATION_ID
          ,unix_timestamp(now())*1000  
          ,unix_timestamp(now())*1000 
          ,NULL 
          ,NULL 
          ,SITE_ID
          ,CONNECTION_TYPE
          ,NETWORK_NAME
          ,2 
          ,ICCID
          ,DATA_PLAN_VALID_DATE
          ,1 
          ,NULL
          ,'automatically'
          );

    insert into t_chap_token (BRIDGE_ID,BRIDGE_SERIAL_NUMBER,TOKEN)
     VALUES (
     (select id from t_bridge where SERIAL_NUMBER = 1234)
     ,'1234'                                                
     ,'XXXXXXXXXXX='                        
END

Trying to execute SQL commands only if value doesn't exists in a column

1 Answers1