Need assistance on SQL server query

[bhaskarpbi99]

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 is different like below

1 parent acc num // 2 customer name // 2 Accounts
1123456789 - NIKE - AAAAA1
1123456789 - NIKES -AAAAA2

but i need to update output like below for all parent account numbers which has different names,

=>> write the query to update that the data for customer name remains the same
1 parent acc num // 1 customer name // 2 Accounts
1123456789 - NIKE - AAAAA1
1123456789 - NIKE -AAAAA2

Could you please assist me to write an update quey to update 1 parent account number  has 1 customer name.

Thanks in advance for your support.

Regards,

Bhaskar

[#2]

1. Use grouping and aggregate function to get a unique customer name.
2. Join account table with the grouping result.
3. 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

@bhaskarpbi99 

[#3]

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

[#4]

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'.

[#5]

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 

[#6]

Hi @rodgerong,

The query is working now . thanks for your valubale support and assistance.
Kudos to you for the solution.