MySQL update if exists else insert throws syntax error

April 12, 2022

Trying to update existing records based not on ID but specific usernames with no luck. I wrote the following query but I got a syntax error I can’t find.

IF EXISTS (SELECT 1 FROM users WHERE username = 'REMOTEUSER1')
    THEN
        BEGIN
            UPDATE users
                SET username = 'REMOTEUSER1', password = 'BJxp98YkVbt4', exp_date = '1650991560', member_id = 1, is_mag = '0', play_token = '', reseller_notes = ''
                WHERE username = 'REMOTEUSER1'
        END;
    ELSE
        BEGIN
            INSERT INTO users (
                username,
                password,
                exp_date,
                member_id,
                is_new,
                play_token,
                reseller_notes
            ) VALUES (
                'REMOTEUSER1',
                'BJxp98YkVbt4',
                '1650991560',
                1,
                0,
                '',
                ''
            )
        END;
    END IF;

>Solution :

If your username column is UNIQUE index or PRIMARY KEY I would use INSERT ... ON DUPLICATE KEY UPDATE Statemen

INSERT INTO users (
    username,
    password,
    exp_date,
    member_id,
    is_new,
    play_token,
    reseller_notes
) VALUES (
    'REMOTEUSER1',
    'BJxp98YkVbt4',
    '1650991560',
    1,
    0,
    '',
    ''
) ON DUPLICATE KEY UPDATE
 password = 'BJxp98YkVbt4',
 exp_date = '1650991560', 
 member_id = 1, 
 is_mag = '0', 
 play_token = '', 
 reseller_notes = '';