CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
Richard S.
Richard S.

Reputation: 743

SQL Update (simple??)

I have 2 tables

Table Customers:

id,
CustomerName

Table CustomerMapping

ID
WrongName,
CorrectedName

What I want to do is :

Update my TableCustomers.CustomerName with the TableCustomerMapping.CorrectName if TableCustomers.CustomerName = TableCustomerMapping.WrongName

I tried this update statement but it is taking way too long, (6+ minutes) before I give up and cancel the query. I should not be taking that long to update 1000 rows.

Here is the Update statement I was trying, am I missing something?

UPDATE i
SET i.CustomerJob = c.CorrectedName
FROM dbo.TableCustomers i
LEFT JOIN dbo.CustomerMapping c ON (i.CustomerJob = c.WrongName);
GO

Upvotes: 0

Views: 100

Answers (5)

Kaf
Kaf

Reputation: 33809

If those two tables related by c.CustomerName = m.WrongName.

Also note, there could be more than one person with same wrong name & correct name. Given the situation (not related by IDs) I think following (INNER JOIN or JOIN) will do the job;

--Do this select first for safety
select c.CustomerName, m.CorrectedName
from TableCustomers c join TableCustomerMapping m on c.CustomerName = m.WrongName

--Update if okay
update c
set c.CustomerName = m.CorrectedName
from TableCustomers c join TableCustomerMapping m on c.CustomerName = m.WrongName

Upvotes: 2

Richthofen
Richthofen

Reputation: 2086

The problem is that you are comparing strings. It's likely your string column has no indexes so for every row in Table 'TableCustomers' the query has to do a table scan on 'CustomerMapping'. You will need to add an index on CustomerMapping on the WrongName field in order for the query optimizer to avoid a table scan and find the data needed without killing performance. So create an index (which if your data set is super large might take some time). I am unsure but you might be able to do a subquery as well and Sort the data by WrongName to aid the query... but the best bet is an index.

Upvotes: 2

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Don't do a LEFT JOIN... if you do that, you will be updating every record in the Customers table, and will set all the unmatched records to NULL... instead, do an INNER JOIN:

UPDATE c
SET c.CustomerName = cm.CorrectedName
FROM dbo.Customers c
INNER JOIN dbo.CustomerMapping cm ON (c.CustomerName = cm.WrongName);

Upvotes: 2

Shortys Oberto Dutari
Shortys Oberto Dutari

Reputation: 556

I understand what you want to do. Try this example:

UPDATE TABLEA
 SET b = TABLEB.b1,
     c = TABLEB.c1,
     d = TABLEB.d1
 FROM TABLEA, TABLEB
 WHERE TABLEA.a = TABLEB.a1
 AND TABLEB.e1 > 40
 GO

I hope you work

Upvotes: 0

Simon Martin
Simon Martin

Reputation: 4231

Perhaps you could add a foreign key to TableCustomerMapping

**TableCustomerMapping**
___________________
ID,
fkCustomerID,
WrongName,
CorrectedName

Then use that to join the 2 tables together and you will update just the 1 record

UPDATE i
SET i.CustomerJob = c.QBName
FROM dbo.TableCustomers i
LEFT JOIN dbo.CustomerMapping c ON (i.ID= c.fkCustomerID);
GO

Upvotes: 0

Related Questions