DELETE Query MySQL

[PriteshP23]

Please look at the table data carefully. I need to delete the rows. I need urgent help..!!

 

PFA...!!

 

Table:

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. :)

 

 

[PriteshP23]

To make it easy to understand:

 

1. for each distinct nidt,noeud pair
2. if there is an operational version then
3. delete all non-operational versions
4. and all older operational versions
5. else
6. delete all older versions

 

I tried this but got error:

DELETE FROM version WHERE version NOT IN (SELECT MAX(VERSION) FROM version GROUP BY ETAT_FONCT)

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

 

 

Thanks in advanced.

[Barand]

Images of your data are difficult to load into a test table. A dump would be easier.

[vinny42]

It's usually easier to describe in SQL what you want to keep, rather than what you want to delete. What you want to delete is then simply "what you have - what you want to keep".

 

If you add a new temporary table and fill it with the records you want to keep, then you can delete the unwanted records from the original table using a LEFT JOIN with the temporatry table.

 

that said; I have rarly had a need to delete records, it is usally much better to build a view that filters all the unwanted records, or even to let the application decide which records it needs. Every record you delete has a tendency to become very important once you have deleted it :-)

[Barand]

PS. your table needs a unique/primary key

ALTER TABLE `version` ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT  FIRST 

, ADD PRIMARY KEY (`id`) ;

Edited September 6, 2013 by Barand

[PriteshP23]

@Guru:

 

Images of your data are difficult to load into a test table. A dump would be easier.

 

I have not taken all the data. Here it is sample for the test:

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;

INSERT INTO `version` (`nidt`, `noeud`, `VERSION`, `ETAT_FONCT`, `idnap`, `nidtint`) VALUES
('00000001B2', 16, 10, 'OPERATIONAL', '1_00000001B2_712_e16', 1012016),
('00000001B2', 16, 11, 'NOT_OPERATIONAL', '1_00000001B2_113_e16', 1012016),
('00000001B2', 17, 11, 'NOT_OPERATIONAL', '1_00000001B2_113_e17', 1012017),
('00000001B2', 17, 10, 'OPERATIONAL', '1_00000001B2_712_e17', 1012017),
('00000001B2', 18, 10, 'NOT_OPERATIONAL', '1_00000001B2_712_e18', 1012018),
('00000001B2', 18, 11, 'OPERATIONAL', '1_00000001B2_113_e18', 1012018),
('00000001E1', 16, 9, 'NOT_OPERATIONAL', '1_00000001E1_313_e16', 1041016),
('00000001E1', 17, 9, 'NOT_OPERATIONAL', '1_00000001E1_313_e17', 1041017))
('00000001E1', 18, 9, 'NOT_OPERATIONAL', '1_00000001E1_313_e18', 1041018),
('00000001F3', 16, 12, 'OPERATIONAL', '1_00000001F3_313_e16', 1053016),
('00000001F3', 16, 11, 'NOT_OPERATIONAL', '1_00000001F3_712_e16', 1053016))

I need to do more operations AFTER deleting these data and this is very urgent.

 

Please help me to solve this problem.

[PriteshP23]

@vinny42:

 

It's usually easier to describe in SQL what you want to keep, rather than what you want to delete. What you want to delete is then simply "what you have - what you want to keep".

 

I have to delete rows in order to do next operations and generate xml file. With LEFT JOIN it seems difficult. It will be best if table has good rows that are needed to do next operations.

[PriteshP23]

@Guru:

 

PS. your table needs a unique/primary key

 

I did. Good remark.

DELETE FROM version WHERE version NOT IN (SELECT MAX(VERSION) FROM version WHERE `ETAT_FONCT` = 'OPERATIONAL' 
GROUP BY ETAT_FONCT)

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

 

Still NOT Working

[PriteshP23]

Well it is not expected at 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:

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

 

:confused:

Edited September 6, 2013 by PriteshP23

[vinny42]

Older MySQL versions will not allow you to select from the same table you are deleting from.

 

But you are getting yourself hung on the idea that you have to delete records. You don't. Al you need is a table that has the records you *do* need, and your XML can be generated from that.

[PriteshP23]

But you are getting yourself hung on the idea that you have to delete records. You don't. Al you need is a table that has the records you *do* need, and your XML can be generated from that.

 

 

In that case also i need to DELETE rows. How to manage this ?

 

I would be greatful to you if we can SOLVED this.

[vinny42]

 

In that case also i need to DELETE rows.

 

No. Stop and think for a minute.

 

What you need is *a* table that holds the records you want. That doesn't have to be the current table, it can be any table.

 

So, you can create a NEW table, fill it with the data you *do* need: INSERT INTO newtable SELECT * FROM oldtable WHERE this=that AND foo=bar;

and use that.

 

If, for some magic reason you absolutely must work with the original table then the solution can stil be the same, you just have to compare the two tables to find the records you need.

[Barand]

This query should give the records to be deleted from your test data

SELECT v.nidt, v.noeud, v.version, v.id
FROM
version v
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)

You could write these results to a temporary table then join the version table to that to either DELETE or UPDATE a deleted_flag. Alternatively you could LEFT JOIN to this query (as a subquery) to select the active records.

 

According to the query, these should be deleted:

nidt	    noeud	version	    id

00000001B2	16	    11	        2
00000001B2	17	    11	        3
00000001B2	18	    10	        5
00000001F3	16	    11	        11

Edited September 6, 2013 by Barand

[PriteshP23]

I'm not sure I follow. I created new table "vtest"

 

CREATE TABLE `vtest` AS SELECT * FROM `version` 

Then i tried to DELETE rows from the vtest table by this query:

DELETE FROM `vtest` WHERE (
SELECT v.nidt, v.noeud, v.version, v.id
FROM version v
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)
)

 

Error: #1241 - Operand should contain 1 column(s)

 

or am I misinterpreting something?  

 

You could write these results to a temporary table then join the version table to that to either DELETE or UPDATE a deleted_flag. Alternatively you could LEFT JOIN to this query (as a subquery) to select the active records.

 

 

I am sorry but I did not get you. Can you give example, please?

 

 

Thank you ALL in advanced for your TIME.  

[Barand]

use

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)

[Barand]

Alternatively, save this version of the query as a VIEW, say "current_version" and use the view instead of the table when required

SELECT v.*
FROM
version v
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') AND (version = maxoper))
    OR
    (isoper IS NULL AND version = maxversion)