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` WH
Solution 1:
Try this...
INSERTINTO 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.
Solution 2:
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' );
Solution 3:
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'ANDEXISTS (SELECT*FROM `admin_rule_module`
WHERE `rule_set_id`='13'AND `entity_id`='2');
INSERTINTO `admin_rule_module`
(`rule_set_id`, `entity_id`, `permission`) VALUES ('13', '2', 'allow')
WHERENOTEXISTS (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.
Solution 4:
If you need a stored procedure to achieve the above then you can rewrite your query like this:
begindeclare count intdefault0;
set count=(SELECT*FROM `admin_rule_module` WHERE `rule_set_id`='13'AND `entity_id`='2');
if count>0thenUPDATE `admin_rule_module`
SET `permission`='allow'WHERE `entity_id`='2' ;
elseINSERTINTO `admin_rule_module` (`rule_set_id`, `entity_id`, `permission`)
VALUES ('13', '2', 'allow') ;
end if ;
end
Post a Comment for "Mysql If Exists"