Update PK in Tble 1 and Tble 2

[DaveT99]

I have two related tables and I have approx 30 records that I wanted to update the PK (intended to change one by one)


I created a simple select query based on the two tables and tried to update the two PK fields but sau cant as related
I tried googling but nothing suitable came up
Can anyone help with a query to change one PK and the related PK is updated automatically
Many Thanks in advance

[orange]

Please provide more info. The purpose of a PK is to uniquely identify a record in a table. It is something that should not be changed generally.
please tell us the exceptional circumstances that require you to change a PK.

In the world of databases, the primary key of a relational table uniquely identifies each record in the table.
 Databases use keys to compare, sort, and store records, and to create relationships between records. 
Choosing the primary key in a database is one of the most important steps in the process.

[DaveT99]

In the data set of 9000 records 8970 have a PK of 9 digits. There is 30 records with a PK from 1 to 30

I wanted to update these PK from 1-30 to a 9 character PK in both tables that are linked to make them a similar structure

They do not referance any other table

Hope that helps

[orange]

Please describe the 2 tables. What o they represent? Why do they need to have the same structure?
How are the 9 digits determined?

[June7]

When linking fields, one is PK and one is FK. Is the PK an autonumber type? It is possible to set value in autonumber field with UPDATE or INSERT sql. Then must run Compact & Repair to reset the auto number seed. Autonumber or not, an UPDATE sql should do want you want.

If these tables are 1-to-1 relation, why have two tables?

[DaveT99]

The data relate to spare parts data that was downloaded from the inventory system to Excel as a single table.
As I will review the data and make changes to descriptions, stock categories etc. I made a 2nd table with all the new fields plus the stock Item No (PK) from table 1

The stock Item No is 9 digits long is unstructured

In the received XLS there are 30 stock item nos coded from 1 to 30 (not sure how who created these..............I want to make these also 9 digits in both tables

I can always remove the table relationships and edit these 30 items in each table, but was hoping there was a better way



Hope this helps

[June7]

Only 30 records? Not autonumber field? I would just manually edit.

Probably have to first remove relationship regardless.

[DaveT99]

Arghhhh
PK as received in Excel short description
why have two tables ............getting more complicated
I have done some freebie master data audits so have a front end with loads of queries that can look at different back ends (representing a freebie audit)
Reason for two tables is table 1 is generic fields and table 2 new data fields or non standard data fields as different companies have different data

Can you clarify if I have stock No in Table 1 and is the PK should the FK also be the Stock Item No or should it be autogenerated???

Many Thanks

Perhaps getting too complicated and I thank everyone for their input and will rethink

[DaveT99]

Okay VIP
I was going to do that but wondered if there was a cleverer way

Thanks for all the help

[June7]

Yes, the clever way is to run UPDATE query, but is it really worth effort to build for only 30 records?