Need help in MySql query INSERT IF NOT EXISTS Otherwise UPDATE

Question

I need you small help, I would like to update the existing record, If it already exists in table otherwise Insert the new record. I am not using the primary key column in the Where clause.

Every time Its insert the new record in the table with the same column 1 and column 2 values.

I have got the few response as well like use the REPLACE. But REPLACE will work for the case of Unique / Primary Key otherwise inserted the new record.

I have used the below query

Method 1:

IF EXISTS (select * from mytable3 WHERE field1 = 'A') THEN
BEGIN
    UPDATE mytable3  
    SET (field1 ='A', field2 = 'DD')  
    WHERE field1 = 'A'  
END
ELSE   
BEGIN
    INSERT INTO  mytable3 (field1, field2) VALUES ('A', 'DD')
END

Method 2:

REPLACE mytable3 (field1, field2) VALUES ('A', 'DD');

Table structure:

CREATE TABLE mytable3 
(
     users int(11) NOT NULL AUTO_INCREMENT, 
     field1 varchar(10), 
     field2 varchar(10), 
     PRIMARY KEY(users)
); 

insert into mytable3 (field1, field2) values('A', 'AA');
insert into mytable3 (field1, field2) values('B', 'BB');
insert into mytable3 (field1, field2) values('C', 'CC');

Note: every time Its insert the new record in the table with the same field 1 and field 2 values

------------------------- Original Question ------------------

I'm struggling to write MySql query to insert new records if not exists, otherwise Update the existing record. But I am facing the Syntax error as below:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT * FROM wnduty.parcel-status-log WHERE parcel-id = 1 AND `sh' at line 1

The SQl Query as below:

IF EXISTS(SELECT * FROM wnduty.`parcel-status-log` WHERE `parcel-id` = 1 AND `shipping-status-id` = 4) 
   THEN 
        BEGIN
            UPDATE wnduty.`parcel-status-log`  SET (`status-date` ='2016-06-10 10:41:58', `is-synced`= 3) 
            WHERE `parcel-id` = 1 AND `shipping-status-id` = 4
        END        
    ELSE
        BEGIN
            INSERT INTO wnduty.`parcel-status-log`  (`parcel-id`,`shipping-status-id`, `is-synced`,`status-date`) 
            values(1, 4, 1, '2016-06-10 10:41:58') 
        END 
END IF   

I have also try by below query but still syntax error..

INSERT INTO wnduty.`parcel-status-log` (`parcel-id`, `shipping-status-id`, `is-synced`, `status-date`) values(1, 4, 1, '2016-06-10 10:41:57')
ON DUPLICATE KEY UPDATE 
`status-date` ='2016-06-13 11:41:58',`is-synced` = 3, `shipping-status-id` = 4 ;

I am using the MySql version as below:

• innodb_version 5.7.12
• protocol_version 10
• version_compile_os Win64

Answer 1

You can use UPDATE INTO with the same syntax as INSERT INTO and if the record is a duplicate on any of the unique keys in the table then it will update the record, otherwise it will insert using the same SQL statement.

Answer 2

Just use REPLACE instead of INSERT, it does exactly what you are looking for ...

INSERT INTO wnduty.parcel-status-log ...

REPLACE wnduty.parcel-status-log ...

From mysql man :

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 14.2.5, “INSERT Syntax”.

Full REPLACE example :

mysql> CREATE TABLE mytable ( users TINYINT(1), field varchar(10), PRIMARY KEY(users));
mysql> insert into mytable values(1, 'A');
mysql> insert into mytable values(2, 'B');
mysql> insert into mytable values(3, 'C');

Now Replace a row with a new value no matter if it exists or not :

mysql> REPLACE mytable (users, field) VALUES (2, "New Value");
mysql> REPLACE mytable (users, field) VALUES (4, "D");