Problem with delete query

[Wendy]

Hi

I am trying to delete records out of the users table where they don't match
the records in the SY020 table. The tables are joined on the usercode
field.

This is my query
DELETE Users.*, sy020.Username
FROM Users LEFT JOIN sy020 ON Users.Username = sy020.Username
WHERE (((sy020.Username) Is Null));

It tells me 'Could not delete from specified tables'

Can anyone show me how to do this please?

Thanks

Wendy

 

[Allen Browne]

Try:
DELETE FROM Users
WHERE NOT EXISTS
(SELECT Username
FROM sy020
WHERE sy020.Username = Users.Username);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Wendy]

Thanks, that works great. I didn't know about the exists command.

Wendy