MySQL Update IF (ROW_COUNT() = 0)

Question

I have this SQL :

UPDATE gcd_data 
SET post_title = 'Hello World', 
    post_content = 'How Are You?',  
    post_date_gmt = '', 
    post_modified_gmt = '',
    post_url = 'www.google.com', 
    post_type = 'product'
WHERE gcd_id='1024'
IF (ROW_COUNT() = 0)
INSERT INTO gcd_data (gcd_id, post_title, post_content, post_date_gmt, 
                   post_modified_gmt, post_url, post_type) 
VALUES ('1024', 'Hello World', 'How Are You?', '', '', 'www.google.com', 'product')

and it gives me error like this :

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF (ROW_COUNT() = 0)
INSERT INTO gcd_data (gcd_id, post_title, post_content, pos' at line 9

I read MySQL documentation about IF statement and I didn't find something wrong with that. so, how to correct this?

Answer 1

I think you should be doing insert on duplicate key update:

INSERT INTO gcd_data (gcd_id, post_title, post_content, post_date_gmt, 
                      post_modified_gmt, post_url, post_type) 
    VALUES (1024, 'Hello World', 'How Are You?', '', '', 'www.google.com', 'product')
    ON DUPLICATE KEY UPDATE 
        post_title = 'Hello World', 
        post_content = 'How Are You?',  
        post_date_gmt = '', 
        post_modified_gmt = '',
        post_url = 'www.google.com', 
        post_type = 'product';

For this to work, you need a unique index on gcd_data(gcd_id). If it is declared as the primary key (which is probably is), then you get this automatically. If not:

create unique index idx_gcd_data_gcd_id on gcd_data(gcd_id);

If the field is not unique, then you should really be rethinking your data model.

Normally, it is considered bad practice to repeat the values multiple times. You can also write this as:

INSERT INTO gcd_data (gcd_id, post_title, post_content, post_date_gmt, 
                      post_modified_gmt, post_url, post_type) 
    VALUES (1024, 'Hello World', 'How Are You?', '', '', 'www.google.com', 'product')
    ON DUPLICATE KEY UPDATE 
        post_title = VALUES(post_title), 
        post_content = VALUES(post_content,  
        post_date_gmt = VALUES(post_date_gmt), 
        post_modified_gmt = VALUES(post_modified_gmt),
        post_url = VALUES(post_url), 
        post_type = VALUES(post_type);