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?

 

[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])=" ";