DELETE Query MySQL

Codes here !

Moderators: macek, egami, gesf

Pritesh23
New php-forum User
New php-forum User
Posts: 25
Joined: Wed Jul 17, 2013 12:42 pm

DELETE Query MySQL

Postby Pritesh23 » Thu Sep 05, 2013 8:43 am

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. :)
Attachments
Table.jpg
Table: version
Table.jpg (90.09 KiB) Viewed 556 times
EXPECTED.jpg
EXPECTED Result
EXPECTED.jpg (47.32 KiB) Viewed 556 times

pbs
New php-forum User
New php-forum User
Posts: 39
Joined: Sat May 11, 2013 1:40 am
Location: Nashik, India
Contact:

Re: DELETE Query MySQL

Postby pbs » Thu Sep 05, 2013 10:31 pm

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
New php-forum User
New php-forum User
Posts: 25
Joined: Wed Jul 17, 2013 12:42 pm

Re: DELETE Query MySQL

Postby Pritesh23 » Fri Sep 06, 2013 12:10 am

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
New php-forum User
New php-forum User
Posts: 25
Joined: Wed Jul 17, 2013 12:42 pm

Re: DELETE Query MySQL

Postby Pritesh23 » Fri Sep 06, 2013 7:12 am

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.

Pritesh23
New php-forum User
New php-forum User
Posts: 25
Joined: Wed Jul 17, 2013 12:42 pm

Re: DELETE Query MySQL

Postby Pritesh23 » Fri Sep 06, 2013 9:24 am

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:

Pritesh23
New php-forum User
New php-forum User
Posts: 25
Joined: Wed Jul 17, 2013 12:42 pm

Re: DELETE Query MySQL

Postby Pritesh23 » Tue Sep 10, 2013 7:04 am

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:


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 1 guest