Solved

MS SQL Server Update Query

Posted on 2014-02-08
6
232 Views
Last Modified: 2014-02-10
MS SQL Server 2012

My database has a UsersProfile table that is related to a Property Definitions table.
Assume property definitions are just for users' contact info such as street, city. email, etc.

So the UsersProfile table looks as follows...

ProfileID
UserID
PropertyDefinitionID
PropertyValue
......

I want a query that will add a row for PropertyDefinition =31 and PropertyValue = 'United States' to each user that does not have such data.



Attached is sample data of the UserProfile table in an xl file.
SQL-Table-Sample-Data.xlsx
0
Comment
Question by:plord1234
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
For starters, give this a whirl..
INSERT INTO UserProfile (ProfileID, UserID, PropertyDefinitionID, Value)
SELECT up.ProfileID, up.UserID, 'United States'
FROM UserProfile up
-- The LEFT insures that all UserProfile rows are selected ... 
LEFT JOIN (
   SELECT ProfileID, UserID 
   FROM UserProfile 
   WHERE PropertyDefition=31 AND PropertyValue='United States') 
      up_us ON up.ProfileID = up_us.ProfileID AND up.PropertyValue = up_us.PropertyValue
-- and then filters for only ones without matching values in up_us
WHERE up_us.ProfileID IS NULL

Open in new window

0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
Comment Utility
Is the ProfileId an identity column?
Do you have a master table for Users? If so, you may use below script:
Insert into UsersProfile (UserID, PropertyDefinitionID, PropertyValue)
Select U.UserId, 31, 'United States'
From Users U
Where Not Exists 
	( Select 1 from UserProfile UP
	  Where UP.UserId = U.UserId and UP.PropertyDefinitionID = 31
	)

Open in new window

-Harish
0
 

Author Closing Comment

by:plord1234
Comment Utility
Another answer may have resolved this issue, however, I did not try it because the solution selected was more concise.  I tried this solution first and it worked perfectly.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Just so you know, ignoring solutions that appear first is going to encourage those experts that posted them to avoid answering your future questions.  

EE expects askers to respond to all comments offered.
0
 
LVL 12

Expert Comment

by:Harish Varghese
Comment Utility
Hello Jim Horn,
I respect your comments and answers, but there were few issues with your query. I would not have posted an answer if your solution was correct. I guess, you have seen the excel sheet the asker posted. If you want to verify your query, please create a temp table with the data provided by @plord1234.
Create Table #userprofile (ProfileId int identity, UserId int, PropertyDefinitionID int, PropertyValue varchar(100))

Insert into #userprofile values(70,23, 'Joan')
Insert into #userprofile values(70,25, 'Burke')
Insert into #userprofile values(70,27, '')
Insert into #userprofile values(70,28, '16 griffiths pond road')
Insert into #userprofile values(70,29, 'Drewster')
Insert into #userprofile values(70,30, 'Massachusetts')
Insert into #userprofile values(70,31, 'United States')
Insert into #userprofile values(70,32, '01547-2344')
Insert into #userprofile values(70,33, '781 258 1486')
Insert into #userprofile values(70,41, 'en-US')
Insert into #userprofile values(71,23, 'Jennifer')
Insert into #userprofile values(71,25, 'Barry')
Insert into #userprofile values(71,27, '')
Insert into #userprofile values(71,28, '15 n.73rd street')
Insert into #userprofile values(71,29, 'Milwaukee')
Insert into #userprofile values(71,30, 'Wisconsin')
Insert into #userprofile values(71,32, '53213-2322')
Insert into #userprofile values(71,33, '414-588-5838')
Insert into #userprofile values(71,41, 'en-US')

Open in new window

And execute your SELECT query. Here is the result (after minor corrections):

ProfileID   UserID      
----------- ----------- -------------
1           70          United States
2           70          United States
3           70          United States
4           70          United States
5           70          United States
6           70          United States
8           70          United States
9           70          United States
10          70          United States
11          71          United States
12          71          United States
13          71          United States
14          71          United States
15          71          United States
16          71          United States
17          71          United States
18          71          United States
19          71          United States

(18 row(s) affected)

There are few issues in your query:
#1. Instead of one row per user, you are getting multiple rows.
#2. UserId 70 already has required row, but the same appears again in the result.
#3. If a user has no entry in this table, that user will not get the new property.

-Harish
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the above post, which I'll look into, but the point I was making is that it is the asker's responsibility to address each comment provided.

I have no objection to your posting a comment after mine with what is believed to be a superior answer.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now