Delete row if exists

Question

DELETE IF EXIST `#__menu`.*
FROM `#__menu` 
LEFT JOIN `#__extensions` ON `#__extensions`.`name` = 'com_view' 
WHERE `#__menu`.`component_id` = `#__xtensions`.`extension_id`
AND `#__menu`.`alias` = 'view-sites' AND `#__menu`.`path` = 'view-sites' AND `#__menu`.`title` = 'View sites';

What is wrong in my sql? I think the problem is in IF EXIST, but i could not figure out how to use it on row.

score 18 · Accepted Answer · answered Oct 12 '12 at 13:16

18

When you're deleting rows from a table, you don't need to use IF EXISTS - you're using a WHERE clause, so if it exists - it will be deleted.

Try changing your query to:

DELETE
FROM `#__menu` 
LEFT JOIN `#__extensions` ON `#__extensions`.`name` = 'com_view' 
WHERE `#__menu`.`component_id` = `#__xtensions`.`extension_id`
AND `#__menu`.`alias` = 'view-sites' AND `#__menu`.`path` = 'view-sites' AND `#__menu`.`title` = 'View sites';

Also, you don't need to specify ```#__menu.*`` (the columns) to be deleted - you'll just needDELETE FROM...`. Check out here for more info regarding the syntax.

answered Oct 12 '12 at 13:16

newfurniturey

37,556
9
94
102

1

but there can be null (just no extension yet with this id) – Kin Oct 12 '12 at 13:20
1

You have `WHERE component_id = extension_id`, it shouldn't be able to return any missing records from `#__menu` or `#__extensions` with that clause; to be on the safe-side, however, you could either change it from a `LEFT JOIN` to a regular `JOIN`, *or* you could add `component_id IS NOT NULL` (and/or) `extension_id IS NOT NULL` to your `WHERE` clause – newfurniturey Oct 12 '12 at 13:24
@Kirix sure thing; Keep me posted and I'll try to help out if it doesn't work =] – newfurniturey Oct 12 '12 at 13:27

Delete row if exists

1 Answers1