Update Query not doing a Find/Replace for me

[JohnnyBGood]

Hi,

I was wondering why my Update query is saying 0 records will be updated.

I started with a Select Query as in Pict1.jpg attached. This returned a couple of hundred records.

I converted the query to an Update Query and added a Part_Id in the Update to field so that I can essentially "find and replace" part id. See Pict2.jpg attached.

Why is it not updating any records?

 

[jzwp22]

What is the datatype of the Part_ID field (text or number)?

Can you provide the SQL text of the 2 queries (switch to SQL view)?

 

[JohnnyBGood]

Hi,

The datatype for PART_ID is text.

The Sql for Select query is:

SELECT SYSADM_REQUIREMENT_Sandbox.PART_ID
FROM SYSADM_REQUIREMENT_Sandbox
WHERE (((SYSADM_REQUIREMENT_Sandbox.PART_ID)="56402") AND ((SYSADM_REQUIREMENT_Sandbox.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT_Sandbox.WORKORDER_BASE_ID) Like '1BR%')) OR (((SYSADM_REQUIREMENT_Sandbox.PART_ID)="56402") AND ((SYSADM_REQUIREMENT_Sandbox.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT_Sandbox.WORKORDER_BASE_ID) Like '1BK%'));

For Update Query is:

UPDATE SYSADM_REQUIREMENT_Sandbox SET SYSADM_REQUIREMENT_Sandbox.PART_ID = "206602"
WHERE (((SYSADM_REQUIREMENT_Sandbox.PART_ID)="56402") AND ((SYSADM_REQUIREMENT_Sandbox.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT_Sandbox.WORKORDER_BASE_ID) Like '1BR%')) OR (((SYSADM_REQUIREMENT_Sandbox.PART_ID)="56402") AND ((SYSADM_REQUIREMENT_Sandbox.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT_Sandbox.WORKORDER_BASE_ID) Like '1BK%'));

 

[jzwp22]

Is the part_ID a key field? If it is not, I think your query should run without issue. If it is, you may have to temporarily remove any relationships, run the query and then re-establish the relationships. I'm not sure how that will impact the integrity of your data. To be on the safe side, I would recommend making a backup copy of the database before you run the update query.

 

[JohnnyBGood]

It's not part of a key... see attached for properties.

And Sandbox is our test database

 

[jzwp22]

I did a test database and the query you provided ran fine, so I am not sure what the issue could be. Are the WORKORDER_TYPE and WORKORDER_BASE_ID fields part of relationships? I do not think that is the issue, but I'm just checking.

 

[GinaWhipp]

Actually it is part of a Relationship as a FK and that is why it won't run, so as jzwp22 says...

If it is, you may have to temporarily remove any relationships, run the query and then re-establish the relationships. I'm not sure how that will impact the integrity of your data. To be on the safe side, I would recommend making a backup copy of the database before you run the update query.

 

[JohnnyBGood]

Yes, it is a foreign key as it is Indexed.. It seems like a difficult task to have to do as quoted though..... especially on this our ERP database

 

[jzwp22]

You will need to look at your relationship window to see what types of joins come off of the SYSADM_REQUIREMENT_Sandbox table. Is this a standalone database? Are the tables linked to another database?

The other option is to post a copy of your database with any sensitive data removed/changed or just a database with some fake data that mimics your real data

 

[GinaWhipp]

Well, unfortunately then you cannot UPDATE it. If you want to see which fields are *in a Relationship* go to the Relationships window. Anything with a line will give you problems to UPDATE because what ever you are trying to UPDATE it to must exist in the main table where the FK's PK resides.

 

[JohnnyBGood]

The database is our ERP (Enterprise Resource Planning) system... I cannot post any of that and can't even fake it up.... It is a large database consisting of many tables. This particular table has foreign keys to several other tables.....