CODEWITHSUNDEEP
sql-serversql-server-2012
Ajay
Ajay

Reputation: 6590

SQL Server query to update one table column value with another table value

I want update one table column value from another table. I have two table i.e. Users, UserProfile I have MobileNumber column in both table. So I want to update Users table mobile number from UserProfile. There may be duplicate mobilenumber in UserProfile table. So I want to avoid duplicate value in Users table. If there is already MobileNumber exists then no need to update.

Here is my query

update Users 
set MobileNumber = up.MobileNumber
from Users u 
left join UserProfile up on u.UserID = up.UserID
where up.MobileNumber not in (select ISNULL(MobileNumber, '') from Users);

But it is not working correctly. In UserProfile table there are some records which contains Null value in MobileNumber. How can I update this column without duplicates?

Upvotes: 1

Views: 764

Answers (2)

Ajay
Ajay

Reputation: 6590

Finally I got solution 

Update u set u.MobileNumber = up.MobileNumber
FROM Users u
JOIN(
 SELECT MobileNumber, MIn(UserId) AS UsID FROm UserProfile
 group by MobileNumber
) up
on u.UserID = up.UsID

Upvotes: 1

gofr1
gofr1

Reputation: 15977

MERGE will help you:

MERGE Users as target
USING (
    SELECT DISTINCT up.UserID, up.MobileNumber 
    FROM UserProfile up 
    WHERE up.MobileNumber NOT IN (SELECT MobileNumber FROM Users WHERE MobileNumber IS NOT NULL)) as source
ON target.UserID = source.UserID
WHEN MATCHED AND target.MobileNumber IS NULL THEN
    UPDATE SET MobileNumber = source.MobileNumber;

But if you got more then 1 MobileNumber for some userid's you must change SELECT DISTINCT UserID, MobileNumber FROM UserProfile WHERE MobileNumber IS NOT NULL part to something like that SELECT DISTINCT UserID, MAX(MobileNumber) FROM UserProfile WHERE MobileNumber IS NOT NULL GROUP BY UserID or write your own query to chose what you need.

Upvotes: 0

Related Questions