MySQL update if exists else insert throws syntax error-CodePudding

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;

CodePudding user response：

The IF-ELSE conditional statement construct is supported to be used through FUNCTIONS or STORED PROCEDURES that can't use it like a normal query.

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 = '';

sqlfiddle

CodePudding user response：

If is_mag is a bit(1) (boolean), then it's likely the update portion where you set it to '0'

either set the boolean to an integer value of 0, or prefix it with b

i.e. my_bool = b'0'