Upsert a table

[Wyoming]

Hello,

I have a temporary table named LEAD_TEMP with a number of records in it and a need to perform an upsert on it.
The fields of this table are ID_LEAD (Numeric), DESCARTADO (Numeric), ID_ESTADO (Numeric).
None of them is a primary key since this table is created through a "create table" query and I don't know how to set a primary key using these queries.

What I am trying to do is to execute the INSERT for those records that don't match the ID_LEAD field and UPDATE the field ID_ESTADO for those records where there is a match for ID_LEAD.

For the Inserts, values to be inserted come from a query. For the Updates, values to be updated come from another table.

I have tried to do this by capturing the 3022 error but id doesn't seem to work since there is no primary key
I have also thought about using Dcount or Dlookup to see if the ID_LEAD already exists in LEAD_TEMP but I don't seem to manage to get to the right syntax and it doesn't work.

Any ideas about this?

Thanks!

[HansV]

A make-table query doesn't let you specify a primary key; you can set the primary key after the table has been created.

For the insert query, try

INSERT INTO LEAD_TEMP (ID_LEAD, DESCARTADO, ID_ESTADO) SELECT ID_LEAD, DESCARTADO, ID_ESTADO FROM [SomeQuery] WHERE ID_LEAD Not In (SELECT ID_LEAD FROM LEAD_TEMP)

using the actual name of the query, of course.

For the update query, try

UPDATE LEAD_TEMP INNER JOIN [OtherTable] ON LEAD_TEMP.ID_LEAD = [OtherTable].ID_LEAD SET LEAD_TEMP.DESCARTADO = [OtherTable].DESCARTADO, LEAD_TEMP.ID_ESTADO = [OtherTable].ID_ESTADO

using the actual name of the other table.

If that doesn't work:

UPDATE LEAD_TEMP SET DESCARTADO = DLookup("DESCARTADO", "[OtherTable]", "ID_LEAD = " & ID_LEAD), ID_ESTADO = DLookup("ID_ESTADO", "[OtherTable]", "ID_LEAD = " & ID_LEAD) WHERE ID_LEAD In (SELECT ID_LEAD FROM [OtherTable])

[Wyoming]

Thank you Hans, this is what I was looking for.
The "Where lead_id not in..." part was the one I was missing. You always make everything seem so simple...

Anyway, I had to change the syntax a little bit since the way you posted took ages to run so I have made some changes:

• First, the query is now a "make-table query" so now the table "EstadosHistoricos_temp" is included in the INSERT instead of the query. But this was not good enough, I had to do another change to make it run faster.
• The other change was to modify the From part into this: FROM LEAD_TEMP RIGHT JOIN EstadosHistoricos_temp ON LEAD_TEMP.ID_LEAD = EstadosHistoricos_temp.ID_LEAD WHERE (((LEAD_TEMP.ID_LEAD) Is Null)). This way it goes really fast.

Thank you very much.

[HansV]

Great - good to hear that you were able to speed up the query.