Jump to content


Photo

DELETE Query MySQL

delete mysql php duplicate

Best Answer Barand, 06 September 2013 - 04:09 PM

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)
Go to the full post


  • Please log in to reply
15 replies to this topic

#1 PriteshP23

PriteshP23

    Advanced Member

  • Members
  • PipPipPip
  • 79 posts
  • Age:24

Posted 05 September 2013 - 09:58 AM

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

 

 

Attached Files


" I will Win not immediately, but Definitely. "

 


#2 PriteshP23

PriteshP23

    Advanced Member

  • Members
  • PipPipPip
  • 79 posts
  • Age:24

Posted 06 September 2013 - 10:15 AM

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


" I will Win not immediately, but Definitely. "

 


#3 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,365 posts
  • LocationCheshire, UK

Posted 06 September 2013 - 11:05 AM

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


moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#4 vinny42

vinny42

    Advanced Member

  • Members
  • PipPipPip
  • 414 posts

Posted 06 September 2013 - 11:36 AM

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



#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,365 posts
  • LocationCheshire, UK

Posted 06 September 2013 - 11:43 AM

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 by Barand, 06 September 2013 - 11:48 AM.

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#6 PriteshP23

PriteshP23

    Advanced Member

  • Members
  • PipPipPip
  • 79 posts
  • Age:24

Posted 06 September 2013 - 11:50 AM

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


" I will Win not immediately, but Definitely. "

 


#7 PriteshP23

PriteshP23

    Advanced Member

  • Members
  • PipPipPip
  • 79 posts
  • Age:24

Posted 06 September 2013 - 11:56 AM

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


" I will Win not immediately, but Definitely. "

 


#8 PriteshP23

PriteshP23

    Advanced Member

  • Members
  • PipPipPip
  • 79 posts
  • Age:24

Posted 06 September 2013 - 11:59 AM

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


" I will Win not immediately, but Definitely. "

 


#9 PriteshP23

PriteshP23

    Advanced Member

  • Members
  • PipPipPip
  • 79 posts
  • Age:24

Posted 06 September 2013 - 12:15 PM

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, 06 September 2013 - 12:15 PM.

" I will Win not immediately, but Definitely. "

 


#10 vinny42

vinny42

    Advanced Member

  • Members
  • PipPipPip
  • 414 posts

Posted 06 September 2013 - 12:18 PM

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.



#11 PriteshP23

PriteshP23

    Advanced Member

  • Members
  • PipPipPip
  • 79 posts
  • Age:24

Posted 06 September 2013 - 12:22 PM

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.


" I will Win not immediately, but Definitely. "

 


#12 vinny42

vinny42

    Advanced Member

  • Members
  • PipPipPip
  • 414 posts

Posted 06 September 2013 - 12:32 PM


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.



#13 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,365 posts
  • LocationCheshire, UK

Posted 06 September 2013 - 01:45 PM

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, 06 September 2013 - 01:56 PM.

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#14 PriteshP23

PriteshP23

    Advanced Member

  • Members
  • PipPipPip
  • 79 posts
  • Age:24

Posted 06 September 2013 - 03:49 PM

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:

" I will Win not immediately, but Definitely. "

 


#15 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,365 posts
  • LocationCheshire, UK

Posted 06 September 2013 - 04:09 PM   Best Answer

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)

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#16 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,365 posts
  • LocationCheshire, UK

Posted 06 September 2013 - 05:07 PM

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)

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com