Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS SQL Server Update Query

Posted on 2014-02-08
6
Medium Priority
?
238 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

718 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