SQL Delete prob.

[Dishan Fernando]

Hi
my prob is like this..
-----------------------------
create table ax(
i int ,
j int
)

create table ay(
i int ,
j int
)

insert into ax values(1,100)
insert into ax values(1,101)
insert into ax values(2,103)

insert into ay values(1,200)
insert into ay values(1,201)
insert into ay values(1,202)
insert into ay values(2,203)
insert into ay values(2,204)
insert into ay values(2,205)

select * from ax
select * from ay
--------------------

I want to delete
2 records(count of ax.i = 1) from ay.i = 1 and
1 record(count of ax.i = 2) from ay.i = 2

expected result :
select * from ay
i , j
-----
1 , any data
2 , any data
2 , any data
note: j is the temporary column in both table.

thanks
dishan

[Hugo Kornelis]

On 12 Jan 2005 23:02:17 -0800, Dishan Fernando wrote:

(snip)

I want to delete
2 records(count of ax.i = 1) from ay.i = 1 and
1 record(count of ax.i = 2) from ay.i = 2

expected result :
select * from ay
i , j
-----
1 , any data
2 , any data
2 , any data
note: j is the temporary column in both table.

Hi Dishan,

DELETE FROM ay
WHERE (SELECT COUNT(*)
FROM ay AS ay2
WHERE ay2.i = ay.i
AND ay2.j < ay.j) <
(SELECT COUNT(*)
FROM ax
WHERE ax.i = ay.i)

SELECT i, j
FROM ay

i j
----------- -----------
1 202
2 204
2 205

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Dishan Fernando]

Thanks.. It works !!!!