Equivalent of the Oracle In (select *)

[Maurice W. Darr]

Does anyone know how to do the equivalent of the Oracle In within Access
SQL?
Oracle will let me do:
delete from TAble_a where (f1,f2) in (select f1,f2 from Table_b)

This incredibly useful syntax lets me tell Oracle to essentally join table a
and b then thow away all the matching rows in table a. Access will delete on
a join but will only let me do so if f1 and f2 are the primary keys.

Thanks in advance,
Maurice

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

JET (Access DB engine) will not do (f1, f2) IN (SELECT...). You have to
do something like this:

DELETE *
FROM tableA As A
WHERE EXISTS (SELECT * FROM tableB WHERE f1 = A.f1 AND f2 = A.f2)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/DeioechKqOuFEgEQJTKACg8vBUl9uE5dJ1SWQUxemoultO6u0AoOXo
A7lubs7vp4sECohD+DLFe86q
=rfNm
-----END PGP SIGNATURE-----

 

[Maurice W. Darr]

Too bad. I know that query but unfortunately it does a linear search of
tableA which is grossly
inefficient. In most cases TableB has only 50 to 100 records while tableA
has 50 to 100 thousand records. I don't want 100,000 random hits when all I
need are 100.



Anyone know if the ANSI 95 SQL standard fixes this or should I move my
Access applications to Oracle;-) Culture clashes I can handle to loss of
substantive functionality if a problem.



Maurice

 

[Michel Walsh]

Hi,


DELETE DISTINCTROW tableA.*
FROM tableA INNER JOIN tableB
ON tableA.f1=TableB.f1 AND tableA.f2=tableB.f2



I am not sure if the SQL standard you mentioned introduced the vector
comparison, or not, but neither Jet, neither MS SQL Server 2000 support it.
I even doubt MS SQL Server 2005 supports it. On the other hand, I was told
that very few versions of Oracle supports the joins in the FROM clause, most
of them still relaying on the *= and =* syntax in the WHERE clause. Not
really "up to the standard", if that rumor is true.



Vanderghast, Access MVP

 

[Maurice W. Darr]

This is what I would expect to work, but in the past this approach only
seemed to work if the primary key for table b (not table A that would make
sense) is f1,f2. Otherwise it gives an error indicating table a can't be
deleted. Is there a way around that? If there is I have a good answer to my
question!

Maurice

 

[Michel Walsh]

Hi,


I just tried it with neither tableA, neither tableB with a primary key at
all in the tables. And it works (that is the role of DISTINCTROW to be able
to track which original row has to be deleted). I am using Jet, with native
Jet tables. Are you using linked tables? Are you using tables or querys and
if querys, are they themselves updateable?



Vanderghast, Access MVP

 

[Maurice W. Darr]

Of course, I missed DISTINCTROW for some reason. Thanks! This was something
that I knew had to be doable. Jumping back and forth between Oracle and
Access I get brain dead sometimes.

Thanks for your help and your continuing hard work. The MVP's have always
been the best part of MS Access.

Maurice

 

[Mark Burns]

Thanks for your help and your continuing hard work. The MVP's have always
been the best part of MS Access.

Hear Hear! I heartily second that thought!