Equivalent of the Oracle In (select *)
in [Queries]
|
Prev: Query Field Heading
Next: DSUM Function Criteria
From: Maurice W. Darr on 13 Feb 2006 14:16 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
From: MGFoster on 13 Feb 2006 14:31 -----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 wrote: > 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.
From: Maurice W. Darr on 13 Feb 2006 14:48 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 "MGFoster" <me(a)privacy.com> wrote in message news:_75If.1362$UN.429(a)newsread2.news.pas.earthlink.net... > -----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 wrote: >> 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. >
From: Michel Walsh on 13 Feb 2006 16:39 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" <mdarr(a)MDSystemsArchitects.com> wrote in message news:eNwMkZNMGHA.1032(a)TK2MSFTNGP11.phx.gbl... > > 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 > > >
From: Maurice W. Darr on 13 Feb 2006 17:40
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" <vanderghast(a)VirusAreFunnierThanSpam> wrote in message news:OcbFJYOMGHA.3272(a)tk2msftngp13.phx.gbl... > 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" <mdarr(a)MDSystemsArchitects.com> wrote in message > news:eNwMkZNMGHA.1032(a)TK2MSFTNGP11.phx.gbl... >> >> 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 >> >> >> > > > |