Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL Server Update Query

Posted on 2014-02-08
6
Medium Priority
?
239 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 66

Expert Comment

by:Jim Horn
ID: 39844142
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 2000 total points
ID: 39844164
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
ID: 39845329
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 39845351
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
ID: 39846664
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 66

Expert Comment

by:Jim Horn
ID: 39847173
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

782 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