Jump to content

DELETE Query MySQL


PriteshP23
Go to solution Solved by Barand,

Recommended Posts

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

 

 

post-164295-0-16270700-1378392754_thumb.jpg

post-164295-0-32269000-1378392767_thumb.jpg

Link to comment
Share on other sites

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: :confused:

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

@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 :confused:

Link to comment
Share on other sites

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: :confused:

Edited by PriteshP23
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 


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.

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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? :confused: 
 
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.   :thumb-up:
Link to comment
Share on other sites

  • Solution

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)
Link to comment
Share on other sites

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)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.