Update based on two tables

[togatown]

Well, I know this doesn't work........any help appreciated.

UPDATE CustomerExt SET FirstName2 =
(SELECT LTRIM(RIGHT(Customer.FirstName, LEN(Customer.FirstName)-(PATINDEX('%&%', Customer.FirstName))))
FROM Customer
LEFT JOIN CustomerExt ON Customer.ID = CustomerExt.ID
WHERE FirstName LIKE '%&%')

 

[gmmastros]

Looks like a parenthesis issue. Try this...

UPDATE CustomerExt SET FirstName2 = 
(SELECT LTRIM(RIGHT(Customer.FirstName, LEN(Customer.FirstName)-(PATINDEX('%&%', Customer.FirstName)))))
FROM Customer
LEFT JOIN CustomerExt ON Customer.ID = CustomerExt.ID
WHERE FirstName LIKE '%&%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[togatown]

Thanks George, worked like a charm. There were 4200+ of these, didn't want to do it manually. Now if I can teach people not to enter the data incorrectly..........