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
Solved

UPDATE statement using nested query

Posted on 2014-07-25
6
429 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 69

Expert Comment

by:Scott Pletcher
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

839 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