CODEWITHSUNDEEP
mysqlsqlmariadb
Toleo
Toleo

Reputation: 774

How do I use IF THEN ELSE to SELECT and INSERT or UPDATE Queries?

I've been trying to use the following query

IF EXISTS (SELECT id FROM users WHERE id = 1) 
THEN 
    (INSERT INTO users (id, username) VALUES (2, user2)) 
ELSE 
    (UPDATE users SET username = 'userUpdated')

But I keep getting 

/* SQL Error (1064): 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 'INSERT INTO users (id) VALUES (2)) ELSE (UPDATE users SET id = 11)' at line 1 */

Also tried using the following query

IF EXISTS (SELECT id FROM users WHERE id = 1) 
THEN 
    (SELECT username FROM users WHERE id = 1) 
ELSE 
    (INSERT INTO users (id, username) VALUES (1, 'user'))

But this time I got

/* SQL Error (1064): 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 'ELSE 
(INSERT INTO users (id, username) VALUES (1, 'user'))' at line 4 */

Am I doing or understood something wrong?

Upvotes: 0

Views: 89

Answers (4)

markusjm
markusjm

Reputation: 2562

In MariaDB 10.1 and newer, you can use compound statements outside of stored procedures. This blog post gives a good description of what you can do with it.

Here's an example the blog:

BEGIN NOT ATOMIC
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN  
   ROLLBACK;
   RESIGNAL;
  END;

  START TRANSACTION;

    stmt1;
    ....
    stmtN;

  COMMIT;
END

Upvotes: 0

ravi polara
ravi polara

Reputation: 572

Try This.

IF EXISTS (SELECT id FROM users WHERE id = 1) 
begin

    INSERT INTO users (id, username) VALUES (2, 'user2')
    end
ELSE 
    UPDATE users SET username = 'userUpdated'

Upvotes: 0

Gareth O'Connor
Gareth O'Connor

Reputation: 60

Are you missing the END IF (and semi colons)?

IF EXISTS (SELECT id FROM users WHERE id = 1) 
    THEN 
    (SELECT username FROM users WHERE id = 1);
ELSE 
    (INSERT INTO users (id, username) VALUES (1, 'user'));
END IF;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The if statement can only be used in programming blocks, such as stored procedures, functions, and triggers.

In any case, MySQL offers simpler syntax for this functionality: insert . . . on duplicate key update.

To use it, id must have a unique index, unique constraint, or be defined as the primary key. Let me assume that a column so-named is already so-defined.

Then:

INSERT INTO users (id, username)
    VALUES (2, user2)
    ON DUPLICATE KEY UPDATE username = 'userUpdated';

Upvotes: 2

Related Questions