Re: delete rows by certain condition

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Sat, 6 Nov 2004 17:23:04 -0000
Message-ID: <McidnVHKZpjnlRDcRVn-qA@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.

--

David Portas
SQL Server MVP
--
Received on Sat Nov 06 2004 - 11:23:04 CST