Page 1 of 1

DELETE Query MySQL

Posted: Thu Sep 05, 2013 8:43 am
by Pritesh23
I need to delete the rows. I need urgent help..!! :help:
Please look at the table data carefully.
PFA...!!

Table:

Code: Select all

    CREATE TABLE IF NOT EXISTS `version` (
    `nidt` varchar(11) NOT NULL,
    `noeud` tinyint(3) NOT NULL,
    `VERSION` float NOT NULL,
    `ETAT_FONCT` varchar(20) default NULL,
    `idnap` varchar(25) NOT NULL,
    `nidtint` bigint(20) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
For SAME nidt & SAME noeud, i would like to DELETE rows based on two condition:

1. If ETAT_FONCT = "OPERATIONAL" Delete REST ROWS EXCEPT HIGHEST Version

2. If ETAT_FONCT != "OPERATIONAL" DELETE Older verions


Thanks in advanced for input. :)

Re: DELETE Query MySQL

Posted: Thu Sep 05, 2013 10:31 pm
by pbs
first check this SELECT query

Code: Select all

SELECT MAX(VERSION) FROM version GROUP BY ETAT_FONCT
if above SELECT query return desired output which you don't want to delete then use this SELECT query as sub query in DELETE query as

Code: Select all

DELETE FROM version WHERE version NOT IN (SELECT MAX(VERSION) FROM version GROUP BY ETAT_FONCT)
may this will help you

TAKE BACKUP OF DATABASE BEFORE USING DELETE QUERY

Re: DELETE Query MySQL

Posted: Fri Sep 06, 2013 12:10 am
by Pritesh23
It seems good. Thanks for quick reply. :)
But if i do so it will DELETE bad rows. :(
Please look at EXPECTED result one more time.

Yes, i took BACKUP and run Your query . It gives error:

#1093 - You can't specify target table 'version' for update in FROM clause

Re: DELETE Query MySQL

Posted: Fri Sep 06, 2013 7:12 am
by Pritesh23
To make it easy to understand:

for each distinct nidt,noeud pair
if there is an operational version then
delete all non-operational versions
and all older operational versions
else
delete all older versions[/color]

I hope it will work. :help:

Thanks.

Re: DELETE Query MySQL

Posted: Fri Sep 06, 2013 9:24 am
by Pritesh23
Well it is not expected at all :(

Code: Select all

    CREATE TABLE IF NOT EXISTS `version` (
    `id` int(11) NOT NULL auto_increment,
    `nidt` varchar(11) NOT NULL,
    `noeud` tinyint(3) NOT NULL,
    `VERSION` float NOT NULL,
    `ETAT_FONCT` varchar(20) default NULL,
    `idnap` varchar(25) NOT NULL,
    `nidtint` bigint(20) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=28546 ;
SQL:

Code: Select all

    DELETE FROM version WHERE version.id NOT IN (SELECT MAX(VERSION) FROM version
    WHERE `ETAT_FONCT` = 'OPERATIONAL'
    GROUP BY `noeud`,`nidt` HAVING MAX(VERSION) IS NOT NULL)
#1093 - You can't specify target table 'version' for update in FROM clause

THANKS IN ADVANCED FOR YOUR TIME.
:help:

Re: DELETE Query MySQL

Posted: Tue Sep 10, 2013 7:04 am
by Pritesh23
Finally, it works..!!! :D

Thank you all for your input.

Code: Select all

DELETE vtest
FROM
vtest
JOIN
    (
    SELECT nidt, noeud, MAX(version) as maxversion
    FROM version
    GROUP BY nidt, noeud
    ) latest USING (nidt, noeud)
LEFT JOIN
    (
    SELECT nidt, noeud, MAX(version) as maxoper, 1 AS isoper
    FROM version
    WHERE etat_fonct = 'OPERATIONAL'
    GROUP BY nidt, noeud
    ) oper USING (nidt, noeud)
WHERE 
    (isoper AND ((etat_fonct<>'OPERATIONAL') OR (version < maxoper)))
    OR
    (isoper IS NULL AND version < maxversion)
:offtopic: