How to delete records from a querie

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I have a query that check unmatched record between two tables.

My query result are 11 records, i would like to dele those records but it
returns me an error:

This is the error: Specif the table containing the records you want to delete.

This is my sql query:
DELETE [8_tbl_Plano_Producao_Linhas_Reais].ID_PPR,
[8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec,
[8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada,
[9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima
FROM 8_tbl_Plano_Producao_Linhas_Reais LEFT JOIN 9_tbl_PPR_Linhas_Temp ON
([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec =
[9_tbl_PPR_Linhas_Temp].COD_Plano_Producao_Cabec) AND
([8_tbl_Plano_Producao_Linhas_Reais].COD_Externo_Materia_Prima =
[9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) AND
([8_tbl_Plano_Producao_Linhas_Reais].Lote = [9_tbl_PPR_Linhas_Temp].Lote)
WHERE ((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada) Is
Null) AND (([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null)) OR
((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada)=0) AND
(([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null));



Regards,
Marco
 
You can only delete from one table at a time.

Remove all references to the table you want to keep from the DELETE clause
except one. Try the following and see if it work

DELETE DISTINCTROW [8_tbl_Plano_Producao_Linhas_Reais].ID_PPR
FROM 8_tbl_Plano_Producao_Linhas_Reais LEFT JOIN 9_tbl_PPR_Linhas_Temp
ON ([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec =
[9_tbl_PPR_Linhas_Temp].COD_Plano_Producao_Cabec) AND
([8_tbl_Plano_Producao_Linhas_Reais].COD_Externo_Materia_Prima =
[9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) AND
([8_tbl_Plano_Producao_Linhas_Reais].Lote = [9_tbl_PPR_Linhas_Temp].Lote)
WHERE ((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada) Is
Null) AND (([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null)) OR
((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada)=0) AND
(([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null));


If that fails, you will probably need to construct a subquery in the where
clause to identify the records you wish to delete.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi. I have a query that check unmatched record between two tables.

My query result are 11 records, i would like to dele those records but it
returns me an error:

This is the error: Specif the table containing the records you want to
delete.

This is my sql query:
DELETE [8_tbl_Plano_Producao_Linhas_Reais].ID_PPR,
[8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec,
[8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada,
[9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima
FROM 8_tbl_Plano_Producao_Linhas_Reais LEFT JOIN 9_tbl_PPR_Linhas_Temp ON
([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec =
[9_tbl_PPR_Linhas_Temp].COD_Plano_Producao_Cabec) AND
([8_tbl_Plano_Producao_Linhas_Reais].COD_Externo_Materia_Prima =
[9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) AND
([8_tbl_Plano_Producao_Linhas_Reais].Lote = [9_tbl_PPR_Linhas_Temp].Lote)
WHERE ((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada) Is
Null) AND (([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null))
OR
((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada)=0)
AND
(([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null));



Regards,
Marco
Click to expand...
 
Hi. I just wanted to delete records from: 8_tbl_Plano_Producao_Linhas_Reais

I solve my problem like this: instead of delete directly. I update my table
to put Null in products code, after I create another one to delete all blanks
product codes.

What do you think?

Marco

John Spencer said:
You can only delete from one table at a time.

Remove all references to the table you want to keep from the DELETE clause
except one. Try the following and see if it work

DELETE DISTINCTROW [8_tbl_Plano_Producao_Linhas_Reais].ID_PPR
FROM 8_tbl_Plano_Producao_Linhas_Reais LEFT JOIN 9_tbl_PPR_Linhas_Temp
ON ([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec =
[9_tbl_PPR_Linhas_Temp].COD_Plano_Producao_Cabec) AND
([8_tbl_Plano_Producao_Linhas_Reais].COD_Externo_Materia_Prima =
[9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) AND
([8_tbl_Plano_Producao_Linhas_Reais].Lote = [9_tbl_PPR_Linhas_Temp].Lote)
WHERE ((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada) Is
Null) AND (([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null)) OR
((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada)=0) AND
(([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null));


If that fails, you will probably need to construct a subquery in the where
clause to identify the records you wish to delete.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Marco said:
Hi. I have a query that check unmatched record between two tables.

My query result are 11 records, i would like to dele those records but it
returns me an error:

This is the error: Specif the table containing the records you want to
delete.

This is my sql query:
DELETE [8_tbl_Plano_Producao_Linhas_Reais].ID_PPR,
[8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec,
[8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada,
[9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima
FROM 8_tbl_Plano_Producao_Linhas_Reais LEFT JOIN 9_tbl_PPR_Linhas_Temp ON
([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec =
[9_tbl_PPR_Linhas_Temp].COD_Plano_Producao_Cabec) AND
([8_tbl_Plano_Producao_Linhas_Reais].COD_Externo_Materia_Prima =
[9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) AND
([8_tbl_Plano_Producao_Linhas_Reais].Lote = [9_tbl_PPR_Linhas_Temp].Lote)
WHERE ((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada) Is
Null) AND (([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null))
OR
((([8_tbl_Plano_Producao_Linhas_Reais].ID_PPR) Like "*") AND
(([8_tbl_Plano_Producao_Linhas_Reais].COD_Plano_Producao_Cabec)=[forms]![17_form_Planos_Producao_Linhas]![id])
AND (([8_tbl_Plano_Producao_Linhas_Reais].Quantidade_Disponibilizada)=0)
AND
(([9_tbl_PPR_Linhas_Temp].COD_Externo_Materia_Prima) Is Null));



Regards,
Marco
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I make this in a query 1
How to choose only the first record from a query 4
Making a Delete Qiuery from an unmatched Query 3
Delete Query: Could not delete from specified tables 0
No Records Deleted Message 1
Deleting Unmatched records 3
Delete Query 1
How to create this query. Not easy 3

Back
Top