Geek asks and answers

How to update data in one column using other TSQL table data

I just realized that I was taking the wrong data for one column in my table. I fixed the problem, however the data that I have captured so far remain incorrect.

What are my tables TableIWantToCorrectandTableWithIDs

As TableIWantToCorrectI have a foreign key TableWithIDs. This is not true.

I can correct the data by comparing the substring of column in TableIWantToCorrectwith column in TableWithIDs.

So now I have

TableIWantToCorrect

Name            ForeignKey
123-abc-123        15
456-def-456        15
789-ghi-789        15

TableWithIDs

CompareName    id
abc            1
def            2
ghi            3

So, I want to update TableIWantToCorrectto have the correct ForeignKey value when the substring in the name is equal to the substring in Compare Compare. The substring position is always the same, so I can use the method Substring.

My attempt:

Update TableIWantToCorrect
SET ForeignKey =
       (SELECT id 
        FROM TableWithIDs 
        WHERE UPPER(CompareName) = UPPER((SUBSTRING(TableIWantToCorrect.Name, 4, 3)))

Result:

1 . , =,! =, <, < =, > , >= . .

, - . ?

+5
3

, UPDATE. , , JOIN UPDATE

UPDATE t1
SET ForeignKey = t2.id
FROM TableIWantToCorrect t1
INNER JOIN TableWithIDs t2
    ON UPPER(t2.CompareName) = UPPER(SUBSTRING(t1.Name, 4, 3))
+13
 Update TableIWantToCorrect
 SET ForeignKey =  s.id
 FROM TableIWantToCorrect , TableWithIDs as s
 WHERE UPPER(s.CompareName) = UPPER( (SUBSTRING(TableIWantToCorrect.Name, 4, 3))
+2
--CREATE FUNCTION dbo.ufn_FindReports 
--(@InEmpID INTEGER)
--RETURNS @retFindReports TABLE 
--(
--    EmployeeID int primary key NOT NULL,
--    FirstName nvarchar(255) NOT NULL,
--    LastName nvarchar(255) NOT NULL,
--    JobTitle nvarchar(50) NOT NULL

--)
----Returns a result set that lists all the employees who report to the 
----specific employee directly or indirectly.*/
--AS
--BEGIN
--WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
--    AS (
--        SELECT e.EmployeeID, e.ManagerID, p.FirstName, p.LastName, P.JobTitle, 0 -- Get the initial list of Employees for Manager n
--        FROM HumanResources.Employee e 
--INNER JOIN Person.Person p 
--ON p.Employeeid = e.EmployeeID
--        WHERE e.EmployeeID = @InEmpID
--        UNION ALL
--        SELECT e.EmployeeID, e.ManagerID, p.FirstName, p.LastName, P.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
--        FROM HumanResources.Employee e 
--            INNER JOIN EMP_cte
--            ON e.ORGANIZATIONNODE.GetAncestor(1) = EMP_cte.OrganizationNode
--INNER JOIN Person.Person p 
--ON p.Employeeid= e.EmployeeID
--        )
---- copy the required columns to the result of the function 
--   INSERT @retFindReports
--   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
--   FROM EMP_cte 
--   RETURN
--END;
--GO

>
-1
source

More articles:


All Articles