how to update foreign key

[masoud_sedighy]

I have 2 tables; design of first table is like below:

Table 1:

id_Doc_line_sheet (pk),Autonumber
DocNo (text)
lineNo (text)
Sheet No (text)

Combination of fields (DocNo, lineNo, Sheet No) is index and unique.

design of second table is like below:

Table 2:

id_Doc_line_trans (pk), Autonumber
id_Doc_line_sheet (fk),Number
name

Now in table1, for the field lineNo I have records with leading spaces and without leading spaces like below:

id_Doc_line_sheet DocNo lineNo Sheet No
1001 doc-0001 line-0001 1
1002 doc-0001 line-0001 1
1003 doc-0001 line-0001 2
1004 doc-0001 line-0001 2
1005 doc-0002 line-0002 1
1006 doc-0002 line-0002 1

And I want deleted these records with leading spaces but at first I want to update (id_Doc_line_sheet) for each unique (DocNo,lineNo,Sheetno) to correct one (without leading space).
I mean if the table2 is like below:

id_Doc_line_trans id_Doc_line_sheet name

1 1001 name01
2 1002 name02
3 1003 name03
4 1004 name04


I update (id_Doc_line_sheet) to something like below:

id_Doc_line_trans id_Doc_line_sheet name

1 1001 name01
2 1001 name02
3 1003 name03
4 1003 name04


And then delete records with leading spaces from first table.
Please help how I can do it?

 

[Pat Hartman]

The example is a little hard to follow because the editor removes multiple spaces and reduces them to a single space. You need to enclose stuff like this in the code tag in order to maintain spacing.

I think the easiest solution is to add a temporary column to table 1 and populate it with the ID of of the FK you want to use. So, the new field would = the PK on the rows where there is no leading space and for the rows with the leading space, the new field would = the PK of the record you want to use. Once you've done that, you can make a simple update query that joins table2 to table1 on the original PK-PD and replace the FK with the new column. Once the update has been done, delete the rows with the leading spaces and remove the temp column.

Things to do before you start this -
1. Make two backups. Zip one of them to prevent accidents.
2. Set up RI if it is not already defined. Remove the Cascade delete checkmark if it exists. You want RI enforced but you do not want Cascade Delete (you can turn it back on later if you want it). This will be a safety check that will prevent you from deleting a row from table1 if there is a row in table2 with a related FK. Of course if the update worked correctly, there would be no errors on the delete query but this is a final check.

 

[arnelgp]

here is your update query for Table2:

Update Table2 INNER JOIN (SELECT table1.id_Doc_line_sheet, (SELECT Top 1 id_Doc_line_sheet FROM Table1 AS T1 WHERE LTRIM(T1.[Sheet No])=Table1.[Sheet No] and Left(T1.[Sheet No],1)=" " And T1.[DocNo]=Table1.[DocNo]) AS Expr1
FROM table1) AS T2 ON table2.id_doc_line_sheet = T2.Expr1 SET Table2.id_doc_line_sheet=T2.id_doc_line_sheet;

just substitute table1 and table2 with
correct name of your tables.

after updating table2, you may delete
those records with leading space ofn [Sheet No] field
from table1.

DELETE table1.* FROM table1 WHERE LEFT([Sheet No])=" ";