Re: INSERT ... WHERE NOT EXISTS ...

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Apr 29, 7:04 pm, KH <K...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi,

I can't seem to get the syntax right for an insert query. In Sql Server I
would do this:

INSERT INTO [bla] (a, b, c)
SELECT 0, 'foo', NULL
WHERE NOT EXISTS(SELECT * FROM [bla] WHERE a = 0)

So, don't insert the new row if there's already a row where (a = 0).
But Access complains that there's no table in the SELECT query.

I tried a derived table to no avail, and this:

INSERT INTO [bla] (a, b, c)
SELECT TOP 1 0, 'foo', NULL
FROM [bla]
WHERE NOT EXISTS(SELECT * FROM [bla] WHERE a = 0)

but that doesn't work if [bla] has no rows.

Anybody know how to do that in Access? Thanks in advance!

- KH

Do you mean that you have two tables/sets which partially overlap
(contain some of the same records) and you just want to add the
missing ones from one set to the other?

If that's the case, the easiest way is to run the "Find unmatched"
query wizard and then turn that into an append query. What it amounts
to is something like this:

SELECT ...
FROM a LEFT JOIN b ON a.joinfield=b.joinfield
WHERE b.joinfield IS NULL

Then you turn that into an append query...

Or did I misunderstand what you were asking (wouldn't be the first
time...)
.

Relevant Pages

  • Parsing XML with ElementTree (unicode problem?)
    ... (this question was also posted in the devshed python forum: ... I'm trying to parse an XML file ... where query is a query to the AWS, and this specific query has the ... some of the content with 'bla bla' string in order to make it fit ...
    (comp.lang.python)
  • RE: INSERT ... WHERE NOT EXISTS ...
    ... SELECT 0, 'foo', NULL ... I can't seem to get the syntax right for an insert query. ... INSERT INTO [bla] ... But Access complains that there's no table in the SELECT query. ...
    (microsoft.public.access.queries)
  • Re: INSERT ... WHERE NOT EXISTS ...
    ... INSERT INTO [bla] ... But Access complains that there's no table in the SELECT query. ... SELECT TOP 1 0, 'foo', NULL ... multiple rows. ...
    (microsoft.public.access.queries)
  • INSERT ... WHERE NOT EXISTS ...
    ... INSERT INTO [bla] ... But Access complains that there's no table in the SELECT query. ... SELECT TOP 1 0, 'foo', NULL ...
    (microsoft.public.access.queries)
  • Re: Crosstab query with date criteria
    ... You must set the query parameters. ... DateTime ... > Does not recognize bla! ...
    (microsoft.public.access.queries)