How to update one columns data using another tables data TSQL

Question

I have just realised I have been capturing the wrong data for one column in my table. I have fixed the problem, however, the data I have captured thus far remains incorrect.

Let's name my tables TableIWantToCorrect and TableWithIDs

In TableIWantToCorrect, I have a foreign key to TableWithIDs. This is what is incorrect.

I am able to correct the data by comparing substring of a column in TableIWantToCorrect with a column in TableWithIDs.

So currently, 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 TableIWantToCorrect to have the correct ForeignKey value when the substring in the Name equals the substring in Compare name. The position of the substring is always the same so I can use the Substring method.

My attempt :

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

The result :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

I know I have done something silly. What have I done incorrectly here ?

Your inline-query is returning more that one row hence the issue. Run this query to figure out which ones are repeating `SELECT CompareName , COUNT(1) FROM TableWithIDs GROUP BY CompareName HAVING COUNT(1) > 1 ` — Chandu, Jul 11 '12 at 14:14
Do you still get the error if you change the subquery to use `SELECT DISTINCT id`? If so there are multiple possible matches and it is ambiguous which one to use. — Martin Smith, Jul 11 '12 at 14:43
possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Cees Timmerman, Jun 05 '14 at 16:55

Taryn · Accepted Answer · 2012-07-11T14:19:56.310

13

The error is because your subquery is returning more than one record for the UPDATE. To fix this, you can do this using a JOIN with your UPDATE

UPDATE t1
SET ForeignKey = t2.id
FROM TableIWantToCorrect t1
INNER JOIN TableWithIDs t2
    ON UPPER(t2.CompareName) = UPPER(SUBSTRING(t1.Name, 4, 3))

edited Jul 11 '12 at 14:19

answered Jul 11 '12 at 14:14

Taryn

242,637
56
362
405

1

I know this is specifically a SQL Server question, but if you want MySQL equivalents for doing this, try here: http://stackoverflow.com/a/11709090/403264 – JonRed Oct 22 '13 at 01:12

score 2 · Answer 2 · answered Jul 11 '12 at 14:14

2

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

answered Jul 11 '12 at 14:14

praveen

12,083
1
41
49

1

You can always tell who works in Oracle, they never use the join command. ;) – Limey Jul 11 '12 at 14:17
1

Unfortunately i swing in between Oracle and Sql server so some time i do get confused ..! nevertheless they yield the same result but still join should be preferred .http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause – praveen Jul 11 '12 at 14:31
1

I was 10 years in Oracle, and I used to hate the join statement! For some reason, I always found it harded to read, but after years in SQL server, I have seen the errors of my ways and always use joins. – Limey Jul 11 '12 at 14:51

score -1 · Answer 3 · edited Jun 24 '15 at 07:23

--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

>

How to update one columns data using another tables data TSQL

3 Answers3