MySQL update if exists else insert throws syntax error

Question

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;

What's the error?

yangqi
– yangqi

2022-04-12 15:46:34 +00:00
Commented Apr 12, 2022 at 15:46 — yangqi
– yangqi, Commented Apr 12, 2022 at 15:46

D-Shih · Accepted Answer · 2022-04-12 16:01:56Z

2

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

edited Apr 12, 2022 at 16:01

answered Apr 12, 2022 at 15:48

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CDoc Over a year ago

It worked! Thanks! But I need to understand why the above didn't work. Could you explain..?

D-Shih Over a year ago

@CDoc I add more detail about that.

whendon · Accepted Answer · 2022-04-12 15:50:11Z

0

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'

answered Apr 12, 2022 at 15:50

whendon

2473 silver badges14 bronze badges

Collectives™ on Stack Overflow

MySQL update if exists else insert throws syntax error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related