How to sync account numbers between two tables using SQL?

Data and Databases
1

Hey everyone, I’m stuck with a database problem. I’ve got two tables: CustomerData and AccountSync. The CustomerData table has ClientID and AccountCode columns. Some AccountCode values are really card numbers, and I need to replace them with the correct account numbers from the AccountSync table.

Here’s what CustomerData looks like:

ClientID AccountCode
101 1234567890
102 9876543210
103 1111222233334444

And here’s AccountSync:

ClientID AccountCode
101 5555666677778888
102 1111222233334444

I tried running this SQL query:

UPDATE CustomerData
SET AccountCode = (SELECT AccountSync.AccountCode
                   FROM AccountSync
                   WHERE CustomerData.ClientID = AccountSync.ClientID)

But it’s not working as expected. It updates some values correctly while replacing others with NULL. Any ideas on how to fix this? I could really use some help. Thanks!

2

hey liam, ur query’s close but not quite there. try using a LEFT JOIN instead:

UPDATE CustomerData cd
LEFT JOIN AccountSync ac ON cd.ClientID = ac.ClientID
SET cd.AccountCode = COALESCE(ac.AccountCode, cd.AccountCode)

this should keep original values when theres no match. lmk if it works!

3

I appreciate your question, Liam.

Your issue is common when dealing with data synchronization. Have you considered using a MERGE statement? It can be particularly effective in these scenarios by updating matching records while leaving others untouched.

Here’s a potential solution:

MERGE INTO CustomerData AS target
USING AccountSync AS source
ON (target.ClientID = source.ClientID)
WHEN MATCHED THEN 
    UPDATE SET target.AccountCode = source.AccountCode;

This approach may offer better performance for large datasets compared to traditional UPDATE statements. I hope this provides a clear direction for resolving your issue.

4

hey liam, have u tried a cte approach?

with ct as (
select clientid, accountcode from accountsync
)
update customerdata
set accountcode = ct.accountcode
from ct
where customerdata.clientid = ct.clientid

thoughts? does it improve clarity?

5

yo liam, here’s another way to tackle it:

update CustomerData c
set AccountCode = (
select a.AccountCode
from AccountSync a
where a.ClientID = c.ClientID
)
where exists (
select 1 from AccountSync a
where a.ClientID = c.ClientID
);

this should update only matched rows. give it a shot!