DELETE Query MySQL

[Pritesh23]

I need to delete the rows. I need urgent 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.

[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

[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

[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.

Thanks.

[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.

[Pritesh23]

Finally, it works..!!!

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)