Solved

UPDATE statement using nested query

Posted on 2014-07-25
6
412 Views
Last Modified: 2014-07-27
The main project is to copy all data from all tables WHERE AgencyID = 1 and INSERT them into the same tables WHERE AgencyID = 30.  I did this for the 'Personnel' and 'Clients' tables.    I am able to do this with a simple INSERT statement.  The problem I am having is when I try to UPDATE the PrimaryProviderID in the clients table.  Each client is assigned a Primary Provider.  

PrimaryProviderID = Personnel.ID

Below is an UPDATE statement I have written to get the ID but it returns multiple rows.  How can I fix this query to only get the ID of the row I want?

UPDATE Clients 
SET PrimaryProviderID = (SELECT p30.ID FROM Clients c INNER JOIN Personnel p1 ON c.PrimaryProviderID = p1.ID 
INNER JOIN Personnel p30 ON p1.LastName = p30.LastName AND p1.FirstName = p30.FirstName)
WHERE AgencyID = 30

Open in new window

0
Comment
Question by:huerita37
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40219589
Using a subquery to set a single value is a poor programming practice, because as you are experiencing if the subquery returns more than one row then the query will throw an error.

I recommend answering your own question by highlighting and executing just the subquery part (below), observing that it returns more than one row, and determining how to handle that.
SELECT p30.ID 
FROM Clients c INNER JOIN Personnel p1 ON c.PrimaryProviderID = p1.ID 
	INNER JOIN Personnel p30 ON p1.LastName = p30.LastName AND p1.FirstName = p30.FirstName

Open in new window

btw if there is more than one Personnel row related to a Client, that would cause this error.

Also, since I don't see a sample data set in this question, and we can't access your data source, then experts here are limited to looking at your T-SQL and making guesses.
0
 

Author Comment

by:huerita37
ID: 40219650
I do understand that the query returns more than one row.  That's where I need help.  What would be the best way to achieve this?  Here is some sample data.    Notice that on the Personnel table the same people are listed with different ID's and AgencyID.  On the clients table the same thing but I changed the Primary ProviderID to reflect the new ID's from the copied data in the Personnel table.  Does this help?

Personnel table:

ID, LastName, FirstName, AgencyID
10, Schulman, Kelly, 1
11, Barnes, James, 1
12, Thompson, Brad, 1

22, Schulman, Kelly, 30
23, Barnes, James, 30
24, Thompson, Brad, 30

Clients table:
ID, Name, PrimaryProviderID, AgencyID
212, Tom Slagter, 10, 1
213, Ben King, 10, 1
214, Danny Pate, 10, 1
215, Vince Pinot, 11, 1

500, Tom Slagter, 22, 30
501, Ben King, 22, 30
502, Danny Pate, 22, 30
503, Vince Pinot, 23, 30
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40219749
You can throw in a "TOP 1" to get just a single ID and an ORDER BY to somehow choose what you mean by TOP, but @Jim is right that this is sketchy and will likely cause you pain later. How do you know what is the right one to choose? Here's I've told it to give you the TOP 1 based on the Personnel's last name, but that's a really random way to do it. Maybe there's a date field so you can choose either the oldest or newest PrimaryProviderID?
UPDATE Clients 
	SET PrimaryProviderID = (
		SELECT TOP 1 p30.ID 
		FROM Clients c 
			INNER JOIN Personnel p1 
				ON c.PrimaryProviderID = p1.ID 
			INNER JOIN Personnel p30 
				ON p1.LastName = p30.LastName AND p1.FirstName = p30.FirstName
		ORDER BY p30.LastName
		)
WHERE AgencyID = 30

Open in new window

0
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.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40219822
The joins as written aren't really logical.  

There's no correlation back to the outer UPDATE query.  You need some link between the two or you'll just randomly assign an ID, which can't be good.  You need to use the Client value from the outer query.

The join from p1 to p30 also isn't entirely logical.  The p1 row will always join to itself in p30, although other rows could join as well.  I would guess the only logical reason to even bother to join to p30 would be that you meant to exclude the "base" p1 row from the join to p30??  Otherwise, why not just use p1.ID?  I'm not sure, since the data isn't clear.


UPDATE c
SET PrimaryProviderID = (
    SELECT TOP (1) p30.ID
    FROM Personnel p1
    INNER JOIN Personnel p30 ON
        --p1.ID <> p30.ID AND --??
        p1.LastName = p30.LastName AND
        p1.FirstName = p30.FirstName
    WHERE
        c.PrimaryProviderID = p1.ID
    )
FROM Clients c
WHERE c.AgencyID = 30
0
 

Author Comment

by:huerita37
ID: 40220511
Here is some better data to show what I am trying to do. I just don't know the easiest/best way to do it.

Personnel Table
ID  AgencyID   LastName FirstName
268	        1	ADMIN	Amos
262	        1	ASHEN	Ceth
2168	1	AUDIT	Newton
1002	1	AUDITOR	Arthur
23	        1	BARTON	Clara

Open in new window


I need to copy everything WHERE AgencyID = 1 and INSERT it into the Personnel table but have the AgencyID = 30.  The result will look like this.

ID  AgencyID   LastName FirstName
3778	30	ADMIN	Amos
3777	30	ASHEN	Ceth
3796	30	AUDIT	Newton
3788	30	AUDITOR	Arthur
3754	30	BARTON	Clara

Open in new window


Next I need to do the same thing with the Clients table.
ID          AgencyID  PrimaryProviderID    LastName     FirstName
17	           1                   268                      APPETITE	Wholesome
11	           1	             268                        ARCHIVE   	Louella
1020           1	             262                         BASTAQUEVICH	Seymour
4 	           1	             262	                     BEHL	        Tinker

Open in new window


Here is a sample what it should look like.  When I copy the Client data how can I get the ID of the PrimaryProvider where AgencyID = 30?
 
ID          AgencyID  PrimaryProviderID    LastName     FirstName
43264	30	              ?                      APPETITE	Wholesome
43270	30	              ?                        ARCHIVE   	Louella
43258	30	             ?                          BASTAQUEVICH	Seymour
43277	30	             ?	                        BEHL	        Tinker

Open in new window


Does this explain my situation better?  I don't know the best way to achieve what I want to do.  Maybe it's not to do a simple INSERT with the basic info and then an UPDATE to get the new PrimaryProviderID.  I just don't know any other way.
0
 
LVL 13

Accepted Solution

by:
Russell Fox earned 500 total points
ID: 40220619
Gotcha. It's going to be difficult without creating a new column like "OldPrimaryProviderID" so you can match the previous value to the new one. You'll need to join on the common columns, FirstName and LastName, and that's going to be imprecise at best:
INSERT INTO Personnel
	SELECT 	ID ,
		30 ,
		LastName ,
		FirstName
	FROM Personnel
	WHERE AgencyID = 1

INSERT INTO Clients (AgencyID, PrimaryProviderID, LastName, FirstName)
	SELECT t1.AgencyID, t2.PrimaryProviderID, t1.LastName, t2.LastName
	FROM Clients t1
		JOIN Clients t2
			ON t1.FirstName = t2.FirstName
			AND t1.LastName = t2.LastName
			AND t1.AgencyID = 1
			AND t2.AgencyID = 30

Open in new window

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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

21 Experts available now in Live!

Get 1:1 Help Now