May 30, 2006 at 9:23 am
Hi All:
I need to update a table and not sure what is the best way to do this. see SQL below:
update cp set cp.active_flag = 'Y'
select c.customer_id, cp.first_name from customer c left outer join cust_personnel as cp on c.customer_id = cp.customer_id inner join customer_csm cc on c.customer_id = cc.customer_id where cc.customer_type_id = 1002 and cc.sales_status_id = 1545 and cp.active_flag is null
--------------------------------------------------------------------------------------
Update c set cc.active_flag = ‘Y’
Set cc.sales_status_id = 1317
select c.customer_id from customer c inner join customer_csm as cc
on c.customer_id = cc.customer_id
where cc.customer_type_id = 1002 and cc.sales_status_id = 1545 and c.active_flag is null
Thanks for your suggestions
William
May 30, 2006 at 10:29 am
I think you have a bit of a problem recognizing that you MUST have a WHERE clause in an UPDATE, else you update an entire table. What you are doing in both sections, (simplistically, as the second Update section probably just errors) is Updating an entire table.
Try the following:
DECLARE @Customer TABLE( Customer_ID integer, Active_Flag char(1))
INSERT INTO @Customer
SELECT 1, NULL UNION ALL
SELECT 2, NULL UNION ALL
SELECT 3, NULL UNION ALL
SELECT 4, NULL UNION ALL
SELECT 5, NULL UNION ALL
SELECT 6, NULL
DECLARE @Customer_CSM TABLE( Customer_ID integer, Active_Flag char(1),
Customer_Type_ID integer, Sales_Status_ID integer)
INSERT INTO @Customer_CSM
SELECT 1, 'N', 1002, 1545 UNION ALL
SELECT 2, 'N', 1003, 1545 UNION ALL
SELECT 3, 'N', 1003, 1545 UNION ALL
SELECT 4, 'N', 1002, 1546 UNION ALL
SELECT 5, 'N', 1002, 1547 UNION ALL
SELECT 6, 'N', 1002, 1545
SELECT * FROM @Customer_CSM
UPDATE @Customer_CSM SET
Active_Flag = 'Y',
Sales_Status_ID = 1317
FROM @Customer_CSM CC
INNER JOIN @Customer C ON( CC.Customer_ID = C.Customer_ID)
WHERE CC.Customer_Type_ID = 1002
AND CC.Sales_Status_ID = 1545
AND C.Active_Flag IS NULL
SELECT * FROM @Customer_CSM
I wasn't born stupid - I had to study.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply