SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Newb needs some Query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wandoctor
Starting Member

USA
4 Posts

Posted - 11/19/2012 :  13:38:18  Show Profile  Reply with Quote
Hello all,

Once I thought I was a pretty logical guy, then SQL came along.

I am in the process of updating some phone numbers in our database. We have a table T_PHONE that needs to be updated and has the following columns,

customer_no (which is the customer ID)
phone_no
phone_type (each customer can have multiple phones, and an int, 1,2 or 3 indicates day, evening or cell)

So any individual customer can have up to three entries in T_PHONE. For instance:

customer_no  phone_no phone_type
111111       555-1212   1
111111       555-1213   3

I have new information that is all phone_type 2 (or evening)that is in a table called NCOA2.

Now, if every customer had a phone_type 2 I think I could handle the task of updating the records. HOWEVER, not every customer has an existing evening phone. So it seems I need to update where there is a type two, and insert a new record for a phone_type of 2 where there isn't.

Any ideas on the best way to construct this?

Thank you so much.

wandoctor
Starting Member

USA
4 Posts

Posted - 11/19/2012 :  13:41:38  Show Profile  Reply with Quote
I knew I'd forget something, there is a column in T_PHONE called entry that is the PK. It needs to auto increment if there is an insert.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/19/2012 :  13:52:27  Show Profile  Reply with Quote
Maybe this:

Insert into T_Phone
(Customer_No,Phone_no,Phone_type)

Select Customer_No,Phone_no,Phone_type
from NCOA2 N
Where NOT Exists ( Select * from T_Phone Where Customer_no = N.Customer_No and Phone_Type = 2)

Update T
Set T.Phone_No = N.Phone_No
from T_Phone T
inner join NCOA2 N on N.CustomerNo = T.Customer_No and T.Phone_Type = N.Phone_Type and T.Phone_Type = 2


Edited by - sodeep on 11/19/2012 13:56:54
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  13:59:09  Show Profile  Reply with Quote
Using sodeep's code, do the updates first and then the inserts. Either way it would work, just that it might be slightly less work for the server if updates are done first.

You can also use MERGE if you are on SQL 2008 or later:
MERGE T_PHONE t
USING NCOA2 a
	ON t.customer_number = a.customer_number
		AND t.phone_type = 2
WHEN matched THEN
	UPDATE SET t.phone_no = a.phone_no
WHEN NOT matched BY TARGET THEN
	INSERT 
		(customer_no,phone_no,phone_type)
	VALUES
		(a.customer_no, a.phone_no, 2);
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  14:00:07  Show Profile  Reply with Quote
quote:
Originally posted by wandoctor

I knew I'd forget something, there is a column in T_PHONE called entry that is the PK. It needs to auto increment if there is an insert.

Is this an identity column? You can find out using this query:
SELECT COLUMNPROPERTY(OBJECT_ID('T_PHONE'),'entry','IsIdentity')
Go to Top of Page

wandoctor
Starting Member

USA
4 Posts

Posted - 11/19/2012 :  14:17:25  Show Profile  Reply with Quote
Thank you both so much for your help. I am using 2008.

I'm pleasantly surprised that I think I understand both answers. Merge looks like it's designed for doing exactly what I need though.

By the way, when I enter the query:

SELECT COLUMNPROPERTY(OBJECT_ID('T_PHONE'),'entry','IsIdentity')

I get 0 in No Column Name
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  15:08:50  Show Profile  Reply with Quote
That means it is not an identity column, so you need to supply the values for the entry column as well. Assuming you just want the next available values, you can change sodeep's insert code as shown in red below

DECLARE @nextId INT;
SELECT @nextId = COALESCE(MAX(entry), 0)
FROM   T_PHONE;

INSERT INTO T_Phone
  (
    Customer_No,
    Phone_no,
    Phone_type,
    entry
  )
SELECT Customer_No,
       Phone_no,
       Phone_type,
       @nextId + ROW_NUMBER() OVER(ORDER BY n.customer_id)
FROM   NCOA2 N
WHERE  NOT EXISTS (
           SELECT *
           FROM   T_Phone
           WHERE  Customer_no        = N.Customer_No
                  AND Phone_Type     = 2
       )

Edited by - sunitabeck on 11/19/2012 15:09:31
Go to Top of Page

wandoctor
Starting Member

USA
4 Posts

Posted - 11/19/2012 :  15:55:27  Show Profile  Reply with Quote
Almost...it seems that customer_no is a FK from T_CUSTOMER
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/19/2012 :  16:00:44  Show Profile  Reply with Quote
Then you need to make insert in T_Customer table first for those entries which doesn't exist.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000