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...
- Use grouping and aggregate function to get a unique customer name.
- Join account table with the grouping result.
- Update customer name in accout table with grouping result.
SQL is below, you can replace the aggregate function MIN by others to get the customer name you want.
CREATE TABLE #account
(
p_acct_no varchar(10),
cust_name varchar(50),
acct_no varchar(10)
)
GO
INSERT INTO #account
VALUES
('1123456789', 'NIKE', 'AAAAA1')
,('1123456789', 'NIKES', 'AAAAA2')
GO
SELECT * FROM #account
UPDATE A
SET
A.cust_name = B.cust_name
FROM #account A
INNER JOIN
(
SELECT p_acct_no, MIN(cust_name) AS cust_name FROM #account
GROUP BY p_acct_no
)B
ON A.p_acct_no = B.p_acct_no
GO
SELECT * FROM #account
DROP TABLE #account
GO
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'.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.