Forum Discussion
Need assistance on SQL server query
Hi All, I have a account table which has parent acct number, Customer name, Account number. the issue is 1 parent account number should have only one customer name even though account number i...
Hi rodgerkong,
Thanks for your support and i think the solution will work for all parent account numbers in account table which has different customer names.
i will check and update you if any further assistance needed.
Kudos for valuable assistance and support,
Regards,
Bhaskar
Hi rodgerkong,,
My account table column names looks like below.(Table Name - [DataTest].[AM_Test])
Parent Acc Num Customer Name Account Num
Q2317256DE KUETTNER GMBH 00004E
Q2317256DE KUETTNER GMBHS E6E148
And when i am executing the below update query getting invalid customer name .
Query used
---------------
UPDATE A
SET
A.[customer Name] = B.[Customer Name]
FROM [DataTest].[AM_Test] A
INNER JOIN
(
SELECT [Parent Acc Num], MIN([Customer Name]) AS cust_name FROM [DataTest].[AM_Test]
GROUP BY [Parent Acc Num]
)B
ON A.[Parent Acc Num]= B.[Parent Acc Num];
Error details
----------------
Msg 207, Level 16, State 1, Line 3
Invalid column name 'customer Name'.
My account table column names looks like below.(Table Name - [DataTest].[AM_Test])
Parent Acc Num Customer Name Account Num
Q2317256DE KUETTNER GMBH 00004E
Q2317256DE KUETTNER GMBHS E6E148
And when i am executing the below update query getting invalid customer name .
Query used
---------------
UPDATE A
SET
A.[customer Name] = B.[Customer Name]
FROM [DataTest].[AM_Test] A
INNER JOIN
(
SELECT [Parent Acc Num], MIN([Customer Name]) AS cust_name FROM [DataTest].[AM_Test]
GROUP BY [Parent Acc Num]
)B
ON A.[Parent Acc Num]= B.[Parent Acc Num];
Error details
----------------
Msg 207, Level 16, State 1, Line 3
Invalid column name 'customer Name'.
In result B, you have give a column alias cust_name to the function MIN. So when you referring it, you must use cust_name instead of Customer Name. bhaskarpbi99
- Hi @rodgerong,
The query is working now . thanks for your valubale support and assistance.
Kudos to you for the solution.
