Solved

MS SQL Server Update Query

Posted on 2014-02-08
6
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

623 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