By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

SQL Delete prob.

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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)
Jul 23 '05 #2

P: n/a
Thanks.. It works !!!!

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.