MySQL Forums
Forum List  »  Newbie

Delete in IF or CASE function
Posted by: Vemsom Helst
Date: June 28, 2017 09:07AM

Hi!
Is there a way to delete certain rows in a table with the use of an IF or CASE function?
Example:
There are two identical rows expect for one column SIGNAL_ID that has 0 respectively 1 (two rows one containing 0 the other 1).
* If there is two rows with one having SIGNAL_ID = 0 and the other 1, I want to delete the row that has 0. Basically If there is one row with either 0 or 1 that's fine.

I have looked around and saw some interesting suggestions to similar questions using subqueries in the where statement or with a join.

Something like this:
Delete from INPUT Where (select if( SIGNAL_ID in (1,0), 1, if(SIGNAL_ID=0, 0, 1))
from 'INPUT TABLE with all duplicate rows on SYS and SIGNAL_ID')

Ive tried this as well but didn't get it to work (Am I on the right path here? Probably need to find duplicate rows here first):
DELETE FROM INPUT
WHERE SIGNAL_ID = 0
AND EXISTS(SELECT 1 FROM INPUT WHERE SIGNAL_ID = 1 LIMIT 1)

Options: ReplyQuote


Subject
Written By
Posted
Delete in IF or CASE function
June 28, 2017 09:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.