CODEWITHSUNDEEP
mysqlsql
Dalton Conley
Dalton Conley

Reputation: 1615

mysql if exists

I've been staring at this for a while now. Maybe some fresh eyes will be able to point out what Im doing wrong.. Here is my query: 

IF (EXISTS (SELECT * FROM `admin_rule_module` WHERE `rule_set_id`='13' AND `entity_id`='2')) 
begin 
UPDATE `admin_rule_module` 
SET `permission`='allow' WHERE `entity_id`='2' 
end
else 
begin 
INSERT INTO `admin_rule_module` (`rule_set_id`, `entity_id`, `permission`) 
VALUES ('13', '2', 'allow') 
end

I'm getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (EXISTS (SELECT * FROM admin_rule_module WHERE rule_set_id='13' AND `enti' at line 1

Upvotes: 3

Views: 7797

Answers (4)

raghavendra v
raghavendra v

Reputation: 107

If you need a stored procedure to achieve the above then you can rewrite your query like this:

begin
  declare count int default 0;
     set count=(SELECT * FROM `admin_rule_module` WHERE `rule_set_id`='13' AND `entity_id`='2');

     if count>0 then 
        UPDATE `admin_rule_module` 
        SET `permission`='allow' WHERE `entity_id`='2' ;

     else
        INSERT INTO `admin_rule_module` (`rule_set_id`, `entity_id`, `permission`) 
          VALUES ('13', '2', 'allow') ;

     end if ;    
end

Upvotes: -1

Jeff Swensen
Jeff Swensen

Reputation: 3573

Edit I think the quickest way to do this is with two serially executed queries like so:

UPDATE `admin_rule_module` 
SET `permission`='allow' 
WHERE `entity_id`='2' 
AND EXISTS (SELECT * FROM `admin_rule_module` 
            WHERE `rule_set_id`='13' AND `entity_id`='2');
INSERT INTO `admin_rule_module` 
  (`rule_set_id`, `entity_id`, `permission`) VALUES ('13', '2', 'allow') 
WHERE NOT EXISTS (SELECT * FROM `admin_rule_module` 
                    WHERE `rule_set_id`='13' AND `entity_id`='2');

The UPDATE doesn't really need the EXISTS clause if you add rule_set_id='13' to your clauses but I don't want to make any assumptions for you.

Upvotes: 0

piotrm
piotrm

Reputation: 12356

In mysql you use BEGIN END only in stored programs, these keywords are invalid for queries. Also IF for queries has different syntax. It seems what you want to do is, since you are trying to update the row with the same value:

INSERT IGNORE INTO admin_rule_module( rule_set_id, entity_id, permission ) 
  VALUES( 13, 2, 'allow' );

Upvotes: 2

David Fells
David Fells

Reputation: 6798

Try this...

INSERT INTO admin_rule_module VALUES (13, 2, 'allow') 
ON DUPLICATE KEY UPDATE permission = 'allow', entity_id = 2;

Assuming you've a UNIQUE KEY key (rule_set_id, entity_id), at least. If you want to do it with a conditional and multiple statements, you have to put it in a PROCEDURE or a FUNCTION.

Upvotes: 4

Related Questions