not in

[Guest]

I wanted to write an update query which ID must exists in table A but not
table B

I use following SQL, but it seems Access doe snot let me use "not in" and
"in" at same time.

"UPDATE MYTBALE set MYTBALE.Mydata = 1
WHERE MYTABLE.MYDATA2 not in (SELECT MYDATA2 from TABLEA where MYTABLE.MYID
= TABLEA.MYID) and
MYTABLE.MYDATA2 in (SELECT MYDATA2 from TABLEB where MYTABLE.MYID =
TABLEB.MYID)"

I can not save this query with "in" clause, but it works if I have "not in"
only.

Any information is great appreciated,

Thanks millions in advance,

 

[John Vinson]

"UPDATE MYTBALE set MYTBALE.Mydata = 1
WHERE MYTABLE.MYDATA2 not in (SELECT MYDATA2 from TABLEA where MYTABLE.MYID
= TABLEA.MYID) and
MYTABLE.MYDATA2 in (SELECT MYDATA2 from TABLEB where MYTABLE.MYID =
TABLEB.MYID)"

Try modeling this as an Outer Join instead:

UPDATE
(MYTABLE LEFT JOIN TableA ON TableA.MYDATA2 = MYTABLE.MYDATA2)
INNER JOIN TableB ON TableB.MYDATA2 = MYTABLE.MYDATA2
SET MYTABLE.Mydata = 1
WHERE TableA.Mydate2 IS NULL;


John W. Vinson[MVP]

 

[Guest]

Thanks millions,

Try modeling this as an Outer Join instead:

UPDATE
(MYTABLE LEFT JOIN TableA ON TableA.MYDATA2 = MYTABLE.MYDATA2)
INNER JOIN TableB ON TableB.MYDATA2 = MYTABLE.MYDATA2
SET MYTABLE.Mydata = 1
WHERE TableA.Mydate2 IS NULL;


John W. Vinson[MVP]