how to update foreign key

Question

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
  1007                         doc-0001    line-0005    1
  1008                         doc-0001     line-0005   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
     5                        1007              name07
     6                        1008              name08

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
 5                     1007                name07
 6                     1007                name08

And then delete records with leading spaces from first table.

Please help how I can do it?

Answer 1

Based on what I understood, it seems you want to update the table2 based on the sheet_no first id used. If so, you can use the following:

Explanation

1. You need to get the first record in table1 based on the sheet_no using report cte
2. Find the corresponding sheet_no for each row in table2
3. Update the table2 entries where row_number =1

Query

;with report as(
   select row_number() over(partition by sheet_no order by id_doc_line_sheet) as [Row],id_doc_line_sheet,sheet_no
   from table1       
   where line_no not like ' %' -- here you can ensure that lin_no doesn't start with leading space
), combined as(
    select t2.id_doc_line_trans,
           t2.id_doc_line_sheet,
           t1.sheet_no
    from table2 t2
    inner join table1 t1 on t2.id_doc_line_sheet = t1.id_doc_line_sheet
)

update t set t.id_doc_line_sheet = r.id_doc_line_sheet
from report r 
inner join combined c on r.sheet_no = c.sheet_no
inner join table2 t on t.id_doc_line_trans = c.id_doc_line_trans
where r.[Row]=1

result after updating table2

    id_doc_line_trans   id_doc_line_sheet   name
    1   1001    name01
    2   1001    name02
    3   1003    name03
    4   1003    name04

Then you can apply the delete statement based on your requirements

Here a working demo

Hope this will help you

Answer 2

Here is an approach which does not use analytic functions, which was the easiest way to approach this in my mind. We can aggregate over table1 and pivot out both the id of the records both with and without leading whitespace. Also, we can check to make sure a given document/sheet even has such a page occurring. Then, all we need to do is join table2 to this first CTE to get the old and new id values in a single record.

WITH cte1 AS (
    SELECT
        DocNo, [Sheet No],
        MAX(CASE WHEN [lineNo] LIKE ' %' THEN id_Doc_line_sheet END) AS id_old,
        MAX(CASE WHEN [lineNo] NOT LIKE ' %' THEN id_Doc_line_sheet END) AS id_new
    FROM table1
    GROUP BY DocNo, [Sheet No]
    HAVING SUM(CASE WHEN [lineNo] LIKE ' %' THEN 1 ELSE 0 END) > 0
),
cte2 AS (
    SELECT
        t1.id_Doc_line_trans, t1.id_Doc_line_sheet, t1.name, t2.id_old, t2.id_new
    FROM table2 t1
    INNER JOIN cte1 t2
        ON t1.id_Doc_line_sheet = t2.id_old
)

UPDATE cte2
SET id_Doc_line_sheet = id_new;

Note that the update logic is trivial; all the information and records of interest were already included in the second CTE.

Demo