Updateable query problem - not permissions!

[JA3395]

I'm having a problem updating a table using a query to select the correct number of records.

When I used a second table to perform the INNER JOIN, the query ran and upated the table, but unfortunately it updated more records (50) than it should have done.

UPDATE XREF_MATNR_TO_PART 
INNER JOIN Part_Master ON XREF_MATNR_TO_PART.Part = Part_Master.pt_part 
SET XREF_MATNR_TO_PART.Part = ""
WHERE (((Part_Master.[Sub-category])="RECOVERED BULK"));

because the "Part" field is not unique.

I tried to use a query to force selection of the correct number of records (25)

qryBISMT:
SELECT qryPart_Master.[Sub-category], qryPart_Master.pt_part, qryPart_Master.MATNR
FROM qryPart_Master
WHERE (((qryPart_Master.[Sub-category])="RECOVERED BULK"));

UPDATE XREF_MATNR_TO_PART 
INNER JOIN qryBISMT ON XREF_MATNR_TO_PART.MATNR = qryBISMT.MATNR 
SET XREF_MATNR_TO_PART.Part = ""
WHERE (((qryBISMT.[Sub-category])="RECOVERED BULK"));

When I test the update query it pulls up the correct records, but when I run the query I get the error:-

"Operation must use an updateable query"

The problem is not permissions as I can update the table, just not by using the statement I want!

J.

 

[PHV]

What is the SQL code of qryPart_Master ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JA3395]

SELECT DISTINCT UCase([Part_Master].[pt_part]) AS pt_part, Part_Master.[PDM Part Num] AS MATNR, Part_Master.[SAP material type], Part_Master.[New Status], Part_Master.pt_status, Part_Master.[Sub-category]
FROM Part_Master
WHERE (((Part_Master.[PDM Part Num]) Is Not Null) AND ((Part_Master.[SAP material type])="BULK") AND ((Part_Master.[New Status])<>"Non SAP")) OR (((Part_Master.[PDM Part Num]) Is Not Null) AND ((Part_Master.[SAP material type])="BULK") AND ((Part_Master.[New Status])="Non SAP") AND ((Part_Master.pt_status) In ("ACTIVE","DEVELOP")));

 

[JA3395]

I'm now trying this:-

UPDATE XREF_MATNR_TO_PART SET XREF_MATNR_TO_PART.Part = ""
WHERE XREF_MATNR_TO_PART.Part IN
(SELECT DISTINCT UCase([Part_Master].[pt_part]) AS Part
FROM Part_Master
WHERE (((Part_Master.[PDM Part Num]) Is Not Null) AND ((Part_Master.[SAP material type])="BULK") AND ((Part_Master.[Sub-category])="RECOVERED BULK") AND ((Part_Master.[New Status])<>"Non SAP")) OR (((Part_Master.[PDM Part Num]) Is Not Null) AND ((Part_Master.[SAP material type])="BULK") AND ((Part_Master.[Sub-category])="RECOVERED BULK") AND ((Part_Master.[New Status])="Non SAP") AND ((Part_Master.pt_status) In ("ACTIVE","DEVELOP"))));

but I'm still getting 50 lines....

J.

 

[PHV]

You have to add some restrictive constraint in the WHERE clause of the UPDATE instruction to get rid of the 25 unwanted rows.
But you said nothing about what was wrong ...

FYI have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JA3395]

>You have to add some restrictive constraint in the
>WHERE clause of the UPDATE instruction to get rid of the
>25 unwanted rows.

That's the problem.

Adding that constraint makes the query not updateable!

>But you said nothing about what was wrong ...

XREF_MATNR_TO_PART TABLE 
Number Part
123456 ABCDEFGH
123457 ABCDEFGH

Part_Master TABLE
part     sub-category
ABCDEFGH RECOVERED BULK
ABCDEFGH FINISHED BULK

This query contains the extracted merged data that is the join between Part_Master and XREF_MATNR_TO_PART

qryPt_mstr QUERY
Number Part
123456 ABCDEFGH
123457 ABCDEFGH

If I put the JOINs in the UPDATE statement then the whole thing doesn't work. If I put the JOINS in the IN clause then I get the Parts I want, but I get both paired Parts and not just the ones that need blanking.

This is so frustrating!

J.