How to perform delete for a selected value in column

mf_arun · New User Joined: 16 Jun 2006 Posts: 1

I need to perform delete for a selected value in column. From the result table, again I need to have deletion only for certain no. of rows. I tried the following query:
----------------------------------------------------------------------------------
DELETE FROM (SELECT col FROM Table1
WHERE col = 'value'
ORDER BY col
FETCH FIRST 3 ROWS ONLY)
----------------------------------------------------------------------------------
in QMF

I 'm getting following error message while trying to execute the above mentioned query.

----------------------------------------------------------------------------------
QUERY MESSAGES:
SQL error at or before ( (line 3, position 13).
----------------------------------------------------------------------------------

Can u please help me on the same. Or can u suggest any other way of doing the same.

prasadvrk · Posted: Fri Jun 16, 2006 5:26 pm

prasadvrk · Posted: Fri Jun 16, 2006 5:32 pm

May be you need to change your query as

DELETE FROM Table1 A WHERE PRIMARYCOL IN
(
SELECT PRIMARYCOL FROM Table1 B
WHERE 3=
(
SELECT COUNT(*) FROM Table1 B
WHERE B.COL ='value'
)
) ;

Let me know if this helps

prasadvrk · Posted: Fri Jun 16, 2006 5:44 pm

I think you need both so your query should look like
DELETE FROM Table1 A WHERE PRIMARYCOL IN
(
SELECT PRIMARYCOL FROM Table1 B
WHERE 3 >
(
SELECT COUNT(*) FROM Table1 B
WHERE B.COL ='value' AND
B.PRIMARYCOL > A.PRIMARYCOL
)
) ;

prasadvrk · Posted: Fri Jun 16, 2006 6:24 pm

Some more improvement to your query , please try this

DELETE FROM Table1 A WHERE PRIMARYCOL IN
(
SELECT PRIMARYCOL FROM Table1 B
WHERE 3 =
(
SELECT COUNT(*) FROM Table1 B
B.PRIMARYCOL > A.PRIMARYCOL
)
AND WHERE B.COL ='value'

) ;