Solved

MS SQL Server Update Query

Posted on 2014-02-08
6
234 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 65

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 65

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

809 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