Re: delete rows by certain condition

From: Linda <linda_shi10_at_yahoo.com>
Date: 10 Nov 2004 10:59:11 -0800
Message-ID: <db0f1d35.0411101059.7cbda25f@posting.google.com>

Thank you very much. It did what I wanted. Now I found I got more than one rows which have max(z) but I only need to keep one of them. How can I delete "duplicated" from the table(of course I have more variables in my real table). For example, I have table t and t1 as follow.

        ID X Y Z
---------- -- ---------- ----------

         1 AA         10          2
         1 AA         10          1
         1 AA         11          2
         2 BB         20          1
         2 BB         20          2
         3 CC         15          2
         3 CC         15          3
         3 CC         20          4
         3 CC         21          4
         3 CC         21          4

       A B
-------- ---
       1 CH1
       2 TH1
       3 TH1
       4 CH1
       5 CH1
       6 CH1
       7 TH1
       8 TH1
       9 MH1
      10 CH1

After apply following code, the TABLE t change to:

        ID X Y Z
---------- -- ---------- ----------

         1 AA         10          2
         1 AA         11          2
         2 BB         20          2
         3 CC         15          3
         3 CC         20          4
         3 CC         21          4
         3 CC         21          4  -- but I don't want this row. 

Can I just use another delete statement to delete it from the table t? How?

Thanks in advance.

Linda

"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message news:<McidnVHKZpjnlRDcRVn-qA_at_giganews.com>...
> You didn't specify any keys or constraints so here are my assumptions:
>
> CREATE TABLE T (id INTEGER NOT NULL, x CHAR(2) NOT NULL, y INTEGER NOT NULL,
> z INTEGER NOT NULL, PRIMARY KEY (id,x,y,z)) ;
>
> CREATE TABLE T1 (a INTEGER NOT NULL, b CHAR(3) NOT NULL /* PRIMARY KEY
> unspecified */) ;
>
> The following is Standard SQL92 but was tested on Microsoft SQLServer 2000
> so you'll have to verify that it will work for you:
>
> DELETE FROM T
> WHERE z NOT IN
> (SELECT MAX(M.z)
> FROM T AS M, T1 AS N
> WHERE M.id = N.a
> AND M.id = T.id
> AND M.x = T.x
> AND M.y = T.y
> GROUP BY N.b) ;
>
> Hope this helps.
Received on Wed Nov 10 2004 - 12:59:11 CST