Forum Discussion

bhaskarpbi99's avatar
bhaskarpbi99
Copper Contributor
Oct 02, 2024

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

 

 

sql server
  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Oct 02, 2024
    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 

    • bhaskarpbi99's avatar
      bhaskarpbi99
      Copper Contributor
      Oct 02, 2024

      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

       

       

      • bhaskarpbi99's avatar
        bhaskarpbi99
        Copper Contributor
        Oct 02, 2024
        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'.



Resources