Update a column from multiple columns in SQL Server

Question

I am trying to do an update in SQL Server. I have a temporary table with 50 users. The table contains a personalid and a date. I want to update the main tables date, with the date from the temporary table based on the personalid of the temp table. The main table has 3000+ users.

The logic is to update the users date (which is different for each user) in the main table using the date from the temp table when the personalid exists in the temp table and the main table.

The query always returns a subquery error for too many records and it tries to update all of the records in the main table.

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

I have tried using cursors, where and for loops and FETCH etc:

BEGIN TRAN

UPDATE MainTable
SET MainTable_Date = (SELECT t.Date FROM TempTable t 
                        INNER JOIN MainTable m 
                        ON t.PersonalId = m.personalid
                        WHERE t.PersonalId = m.personalid)  
FROM 
    MainTable AS m
INNER JOIN 
    TempTable t ON m.personal_id = t.PersonalId
WHERE 
    m.personal_id IN (SELECT personalId FROM TempTable)

ROLLBACK TRAN

Why use the alias m twice for MainTable? And t? Your subquery is thus made independent of the outer query. — HABO
– HABO, Commented Dec 24, 2015 at 14:02

Devart · Accepted Answer · 2015-12-24 10:45:05Z

2

If this fails:

UPDATE m
SET MainTable_Date = t.[Date]
FROM MainTable AS m
INNER JOIN TempTable AS t ON m.personal_id = t.PersonalId

it means that there is not an one-on-one relationship on the tables.

For checking run:

SELECT
    m.personal_id,
    t.PersonalId
FROM MainTable AS m
LEFT JOIN TempTable AS t ON m.personal_id = t.PersonalId
ORDER BY M.personal_id, t.PersonalId

edited Dec 24, 2015 at 10:45

Devart

120k23 gold badges172 silver badges189 bronze badges

answered Dec 24, 2015 at 10:38

shadow

1,9031 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Over a year ago

I do not think the update will fail even if the personal_id occurs more than once in the TempTable. It will just be unclear which value in the temptable will be used if there is more than one occurrence. At least the first statement in your answer solves a lot of no no's in the original query.

N.Dinesh.Reddy · Accepted Answer · 2015-12-25 05:19:59Z

1

This query will satisfy ur need i think

CREATE TABLE MainTable
(
Personal_id int,
[MainTable_Date] date
)



CREATE TABLE #TempTable
(
Personalid int,
[Date] date
)



INSERT INTO MainTable
VALUES(1,'20120202'),(2,'20130202'),(3,'20150202'),(4,'20150206')

INSERT INTO #TempTable
VALUES(1,'20120203'),(2,'20130311'),(3,'20100202')


SELECT * FROM MainTable

SELECT * FROM #TempTable

UPDATE MainTable
SET MainTable_Date=(SELECT [DATE] FROM #TempTable AS T WHERE T.Personalid=M.Personal_id)
FROM MainTable AS M 
WHERE M.Personal_id IN (SELECT Personalid FROM #TempTable)




SELECT * FROM MainTable


DROP TABLE MainTable

DROP TABLE #TempTable

answered Dec 25, 2015 at 5:19

N.Dinesh.Reddy

6322 gold badges9 silver badges15 bronze badges

Comments

marc_s · Accepted Answer · 2015-12-24 12:03:08Z

1

The problem is that we don't really know what is behind this part of the SQL query.

SELECT 
    t.Date 
FROM 
    TempTable t 
INNER JOIN 
    MainTable m ON t.PersonalId = m.personalid
WHERE 
    t.PersonalId = m.personalid

This part of the query seems to give you more than one value when you try to SET it into the MainTable_Date. You have to give your query more details to get only one value. Maybe you should use a MAX(..) value or try to aggregate your data.

edited Dec 24, 2015 at 12:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 24, 2015 at 10:39

Vincent G

3611 gold badge5 silver badges22 bronze badges

Collectives™ on Stack Overflow

Update a column from multiple columns in SQL Server

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related