DELETE Query

[PriteshP23]

PFA...!!

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

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

[Reverend Jim]

I'm not sure I follow. If I reword your conditions (and keep the same meaning, I hope) I get

1. delete older versions if OPERATIONAL
2. delete older versions if NOT OPERATIONAL

assuming that delete REST ROWS EXCEPT HIGHEST version means delete all rows except that row for the highest version. The code for that is

DELETE FROM version
 WHERE VERSION < MAX(VERSION)

or am I misinterpreting something?

[PriteshP23]

@Reverend Jim: It seems good. Thanks for quick reply.

But if i do so it will DELETE bad rows.

or am I misinterpreting something?

Please look at EXPECTED result one more time.

• IF I run your query it will delete First row (See Expected Result) with version 10 which should not be deleted.

[Reverend Jim]

Sorry. I did not notice the attached images although I'm not sure that clarifies things for me. It still boils down to (regardless of the other conditions)

if OPERATIONAL then 
    delete older versions
else
    delete older versions

What is the difference between
Delete REST ROWS EXCEPT HIGHEST Version
and
DELETE Older verions

Basically there is a disconnect between what you are saying and the pictures. Based solely on the pictures I get

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

Is that more in line with what you want?

[PriteshP23]

Yes, exactly. That's perfect. You understand perfectly....!!

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

Please let me know the query.

I tried this one 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.

[PriteshP23]

Still NOT Working

    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

[Reverend Jim]

I don't know if this is possible to do in a single query. Even if possible I suspect it would be so unreadable that it would be wrong to do it. The pseudocode for doing it with multiple queries would be

get all the records sorted by nidt,noeud

step through all the records
    if this is a new nidt-noeud pair
        if there are any OPERATIONAL versions
            get the highest OPERATIONAL version for this nidt-noeud pair
            delete all NOT OPERATIONAL versions and all older OPERATIONAL versions for this nidt-noeud pair
        else
            get the highest version for this nidt-noeud pair
            delete all older versions for this nidt-noeud pair
        end
    end
end

[Reverend Jim]

It's raining and I'm bored. Here's the code in vbscript using ADO.

set conn = CreateObject("ADODB.Connection")
set rec1 = CreateObject("ADODB.RecordSet")
set rec2 = CreateObject("ADODB.RecordSet")

conn.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
rec1.Open "SELECT * FROM version ORDER BY nidt,noeud", conn, 3

lastpair = ""

'scan for all distinct nidt-noeud pairs

do until rec1.EOF

    nidt  = rec1("nidt").Value
    noeud = rec1("noeud").Value

    nextpair = nidt & noeud

    if nextpair <> lastpair then

        'get the number of operational versions

        qry = "SELECT COUNT(*) from version " _
            & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud _
            & "   AND ETAT_FONCT = 'OPERATIONAL'"
        rec2.Open(qry), conn, 1
        numop = rec2(0).Value
        rec2.Close

        if numop > 0 then

            'there is at least one operational version - get the highest operational version

            qry = "SELECT MAX(VERSION) from version " _
                & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud _
                & "   AND ETAT_FONCT = 'OPERATIONAL'"
            rec2.Open(qry), conn, 1
            maxver = rec2(0).Value
            rec2.Close

            qry = "DELETE FROM version " _
                & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud _
                & "   AND (ETAT_FONCT = 'NOT_OPERATIONAL' " _
                & "    OR  VERSION < " & maxver & ")"

        else

            'no operational versions - get the highest version

            qry = "SELECT MAX(VERSION) from version " _
                & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud
            rec2.Open(qry), conn, 1
            maxver = rec2(0).Value
            rec2.Close

            qry = "DELETE FROM version " _
                & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud _
                & "   AND VERSION < " & maxver

        end if

        'do the delete

        conn.Execute(qry)
        lastpair = nextpair

    end if

    rec1.MoveNext

loop

rec1.Close
conn.Close

[1stDAN]

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

There is a very mysql-specific problem: You want to delete (kind of update as mention in error #1093) some rows of table version whereas you reference table version within the where clause (SELECT MAX(VERSION) FROM version...). Unfortunately, this is not allowed in MySQL. (Not a problem for all other database systems I know.) You may check this: Click Here There you will also find a work around.

Note btw: SELECT ... WHERE VERSION < MAX(VERSION) will not work for aggregate functions (max, sum, count, ...) are not allowed in where clause, however, they may appear in having clause. This is an ANSI-SQL requirement.

[PriteshP23]

@Reverend Jim:

It's raining and I'm bored. Here's the code in vbscript using ADO.

Thanks for your time and great effort. I am sorry I do not know VBScript at all.

@1stDAN:
You are right.
Well, i believe it can be achieve by SINGLE Query. I do not know HOW but there is a possible solution.

[Reverend Jim]

You can take the code almost line for line and put it into vb.net. Incidentally, I created your database (with the given data) and ran the code against it and got the results in your example.

[Reverend Jim]

Still raining and still bored. Heres the vb.net code. Note - you will have to add a reference to adodb under the .NET tab.

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim conn As New ADODB.Connection
        Dim rec1 As New ADODB.Recordset
        Dim rec2 As New ADODB.Recordset

        conn.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
        rec1.Open("SELECT * FROM version ORDER BY nidt,noeud", conn, 3)

        Dim lastpair As String = ""
        Dim nextpair As String = ""
        Dim nidt As String
        Dim noeud As String
        Dim qry As String
        Dim numop As Integer
        Dim maxver As Integer

        'scan for all distinct nidt-noeud pairs

        Do Until rec1.EOF

            nidt = rec1("nidt").Value
            noeud = rec1("noeud").Value

            nextpair = nidt & noeud

            If nextpair <> lastpair Then

                'get the number of operational versions

                qry = "SELECT COUNT(*) from version " _
                    & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud _
                    & "   AND ETAT_FONCT = 'OPERATIONAL'"
                rec2.Open(qry, conn, ADODB.CursorTypeEnum.adOpenStatic)
                numop = rec2(0).Value
                rec2.Close()

                If numop > 0 Then

                    'there is at least one operational version - get the highest operational version

                    qry = "SELECT MAX(VERSION) from version " _
                        & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud _
                        & "   AND ETAT_FONCT = 'OPERATIONAL'"
                    rec2.Open(qry, conn, ADODB.CursorTypeEnum.adOpenStatic)
                    maxver = rec2(0).Value
                    rec2.Close()

                    qry = "DELETE FROM version " _
                           & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud _
                           & "   AND (ETAT_FONCT = 'NOT_OPERATIONAL' " _
                           & "    OR  VERSION < " & maxver & ")"

                Else

                    'no operational versions - get the highest version

                    qry = "SELECT MAX(VERSION) from version " _
                     & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud
                    rec2.Open(qry, conn, ADODB.CursorTypeEnum.adOpenStatic)
                    maxver = rec2(0).Value
                    rec2.Close()

                    qry = "DELETE FROM version " _
                           & " WHERE nidt = '" & nidt & "' AND noeud = " & noeud _
                     & "   AND VERSION < " & maxver

                End If

                conn.Execute(qry)
                lastpair = nextpair

            End If

            rec1.MoveNext()

        Loop

        rec1.Close()
        conn.Close()

    End Sub

End Class

[PriteshP23]

Finally, it works..!!! :) :)

Thank you all for your input.

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)