| Author |
 Topic  |
|
|
wandoctor
Starting Member
USA
4 Posts |
 Posted - 11/19/2012 : 13:38:18
|
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
|
| 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. |
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/19/2012 : 13:52:27
|
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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 13:59:09
|
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); |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 14:00:07
|
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') |
|
|
|
wandoctor
Starting Member
USA
4 Posts |
Posted - 11/19/2012 : 14:17:25
|
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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 15:08:50
|
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 |
|
|
|
wandoctor
Starting Member
USA
4 Posts |
Posted - 11/19/2012 : 15:55:27
|
| Almost...it seems that customer_no is a FK from T_CUSTOMER |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/19/2012 : 16:00:44
|
| Then you need to make insert in T_Customer table first for those entries which doesn't exist. |
|
|
| |
Topic |
|
SQL Newb needs some Query help
