[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Conditional Update taking fields from Table B to Table A

Posted on 2011-10-24
2
Medium Priority
?
237 Views
Last Modified: 2012-05-12

Some background info:

This is being done in an Access 2003 ADP interacting with SQL Server 2000. I am using a stored procedure to accomplish this, and it only needs to execute once, then the SP will be deleted.
Table A is a local login table that stores a users unique ID, login ID, password, name and email.

   UserID |    LoginID    | UserPass | Name       | UserEmail
1|    1       |     abc         |  xyz             | Dude 1     | dude1@xyz.com
2|    2       |    123          |  456          | Lady 1     |
3|    3       |     789         |   012         | Lady 2     | Lady2@xyz.com
4|    4       |     test         |   test1       | Dude 2     |


Table B is a table that stores user communication information (Email, Phone, or fax). This is accomplished by a character that denotes what sort of communication the associated row is and then the string of data itself.
   UserID |  CommType  |  CommValue
1 |    1      |      P              |  1234567890
2 |    1      |      E              |  dude1@xyz.com
3 |    2      |      E              |  lady1@xyz.com
4 |    2      |      E              |  ladywoman.1@gmail.com
5 |    2      |      F              |  5621237895
6 |    3      |      P              |  9876543210
7 |    3      |      F              |  4562137894
8 |    3      |      E              |  lady2@xyz.com
9 |    3      |      E              |  luckbealady@msn.com
10|    4     |      E              |  dude2@xyz.com
11|    4     |      P              |  1238675309
etc....

The problem:

I am trying to update the email field in Table A with an email from Table B based on 2 conditions:
1. The email field in Table A is null
2. The user only has 1 email address in Table B

I have mainly been trying UPDATE queries with 2 subqueries, one in SET UserEmail = subquery and then a WHERE EXISTS  subquery but that gives me a "Subquery returned more than 1 value" error. I also briefly experimented with using an INNER JOIN in the subquery, but I get syntax errors near the GROUP BY clause.

Any help attacking and besting this would be very appreciated. Thanks!

Below is the SQL to create the test environment I'm working in.

CREATE TABLE tableA
(
UserID		int,
LoginID		nvarchar(50),
UserPass	nvarchar(50),
Name 		nvarchar(50),
Email		nvarchar(100)
);

CREATE TABLE tableB
(
UserID		int,
CommType	char(1),
CommValue	nvarchar(100)
);

INSERT INTO tableA (UserID, LoginID, UserPass, Name, Email) Values
(1, 'abc', 'xyz', 'Dude 1', 'dude1@xyz.com');

INSERT INTO tableA (UserID, LoginID, UserPass, Name, Email) Values
(2, '123', '456', 'Lady 1', NULL);

INSERT INTO tableA (UserID, LoginID, UserPass, Name, Email) Values
(3, '789', '012', 'Lady 2', 'Lady2@xyz.com');

INSERT INTO tableA (UserID, LoginID, UserPass, Name, Email) Values
(4, 'test', 'test1', 'Dude 2', NULL);

INSERT INTO tableB (UserID, CommType, CommValue) Values
(1, 'P', '1234567890');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(1, 'E', 'dude1@xyz.com');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(2, 'E', 'lady1@xyz.com');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(2, 'E', 'ladywoman.1@gmail.com');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(2, 'F', '5621237895');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(3, 'P', '9876543210');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(3, 'F', '4561237894');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(3, 'E', 'lady2@xyz.com');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(3, 'E', 'luckbealady@msn.com');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(4, 'E', 'dude2@xyz.com');

INSERT INTO tableB (UserID, CommType, CommValue) Values
(4, 'P', '1238675309');

Open in new window

0
Comment
Question by:mhimmler
2 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 2000 total points
ID: 37018613
UPDATE TableA SET UserEmail = Tableb.CommValue
FROM TableA
INNER JOIN TableB ON TableA.UserID = TableB.UserID
WHERE TableB.UserID IN (SELECT UserID FROM TableB WHERE CommType = 'E' GROUP BY UserID HAVING COUNT(*) = 1)
AND TableA.UserEmail IS NULL
AND TableB.CommType = 'E'

0
 

Author Closing Comment

by:mhimmler
ID: 37018703
Nailed it. Query did exactly what I need it to do. Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

872 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