Update Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update Issue

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2012-03-07 : 03:55:11

Hi, I have the following query:


SELECT 
  A.Company_ID, A.Staff_ID, A.Customer_ID, A.Effective_From, A.Effective_To 
  ,B.Staff_ID, B.Effective_From, B.Effective_To 
FROM CUSTOMER.RT_Customer_Consultant A 

INNER JOIN
CUSTOMER.RT_Customer_Consultant B
ON A.Customer_ID = B.Customer_ID
AND A.Effective_To = B.Effective_From 
AND B.Customer_Consultant_Status = 'A' And B.Status ='0' 
AND B.SNO <> A.SNO 


WHERE A.Customer_Consultant_Status = 'A' And A.Status ='0'
order by A.CUSTOMER_ID, A.Effective_From

May I know how can I modify the query so that it will update A.Effective_To = '2012-03-07' if the above scenario was found?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-03-07 : 03:58:37

Easiest way...


update RT_Customer_Consultant
set A.Effective_To = '2012-03-07' 
FROM
CUSTOMER.RT_Customer_Consultant RT_Customer_Consultant
INNER JOIN
( 
SELECT 
  A.Company_ID, A.Staff_ID, A.Customer_ID, A.Effective_From, A.Effective_To 
  ,B.Staff_ID, B.Effective_From, B.Effective_To 
FROM CUSTOMER.RT_Customer_Consultant A 

INNER JOIN
CUSTOMER.RT_Customer_Consultant B
ON A.Customer_ID = B.Customer_ID
AND A.Effective_To = B.Effective_From 
AND B.Customer_Consultant_Status = 'A' And B.Status ='0' 
AND B.SNO <> A.SNO 


WHERE A.Customer_Consultant_Status = 'A' And A.Status ='0'
order by A.CUSTOMER_ID, A.Effective_From
) A
ON
RT_Customer_Consultant.Customer_ID = A.Customer_ID

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2012-03-07 : 04:05:59

Hi, I tried execute it and get an error

Msg 8156, Level 16, State 1, Line 25
The column 'Staff_ID' was specified multiple times for 'A'.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-03-07 : 04:08:07

Take it out of the select list then, and effective_From and Effective_To!

So change:

A.Company_ID, A.Staff_ID, A.Customer_ID, A.Effective_From, A.Effective_To
,B.Staff_ID, B.Effective_From, B.Effective_To

To:

A.Company_ID, A.Staff_ID, A.Customer_ID, A.Effective_From, A.Effective_To

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-03-07 : 04:08:54

In fact, all you need is A.Company_ID on that line.

sqllearner05
Starting Member

9 Posts

Posted - 2012-03-07 : 04:20:57

try below query
UPDATE A
SET A.Effective_To = '2012-03-07'
FROM CUSTOMER.RT_Customer_Consultant A
INNER JOIN CUSTOMER.RT_Customer_Consultant B
ON A.Customer_ID = B.Customer_ID
AND A.Effective_To = B.Effective_From
AND B.Customer_Consultant_Status = 'A'
AND B.Status ='0'
AND B.SNO <> A.SNO
WHERE A.Customer_Consultant_Status = 'A' And A.Status ='0'
ORDER BY A.CUSTOMER_ID, A.Effective_From

sqllearner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources