On 11/24/05, polash26 <polash26@...> wrote:

> Hmm, Even I'm not 100% clear about that MS SQL query, from my
> understand that query will delete data from T1, but not too sure

I try to create a view a then delete from t1, someting like this:

-------------------------------------------
create view v1 (id,recent) as
select id,count(*) from t1 where id in (select id from t2) group by id;

delete from t1 where id in (
select v1.id from v1, t2 where v1.id=t2.id and v1.recent > t2.recent
);
-------------------------------------------

but, a) is very ineficent because is evaluated with each row and b)
don't work :(

When a test it with this data:
-------------------------------------------
create table t1 (
id integer );

create table t2 (
id integer,
recent integer);

insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (3);
insert into t1 values (3);
insert into t1 values (4);
insert into t1 values (4);
insert into t1 values (4);
insert into t1 values (4);

insert into t2 values (1,2);
insert into t2 values (2,2);
insert into t2 values (3,2);
insert into t2 values (4,2);
-------------------------------------------

a got this result:

-------------------------------------------
SQL> delete from t1 where id in (select v1.id from v1, t2 where
v1.id=t2.id and v1.recent > t2.recent);
SQL> select * from t1;

ID
============

1
2
2
3
3
4
4
-------------------------------------------

when i supouse must be:

-------------------------------------------
SQL> rollback;
SQL> delete from t1 where id in (3,4);
SQL> select * from t1;

ID
============

1
2
2
-------------------------------------------

> about that "where" clause ??? This is one of the dotnetnuke store
> procedure for MS SQL. I'm looking for equivalent FireBird query.

Mybe with a store procedure, but don't so versatil like your example.

--
Saludos,
Toño
----
http://agc.com.mx/antoniogc/blog