SQL Updates(concatenation)

[Brenda1231]

Hi all.

Okay I am trying to update an Access Database using SQL.
I need to change a number of area codes, but leave the rest of the number alone.
In other words the numbers are stored (111)222-3333

This is what I am trying, but it is not working:

UPDATE Employees
SET LEFT$(HomePhone, 5) = '(222)'
WHERE LEFT$(HomePhone, 5) = '(111)';

Any help where I might be going wrong???

Thanks


:(

[qball]

I think so...


UPDATE Employees
SET LEFT$(HomePhone, 5) = '(222)'
WHERE LEFT$(HomePhone, 5) = '(111)';


you can't 'dynamically' change a column value using a function in the manner you are trying. The function (LEFT$) returns a value from the parameter (col), so workey in WHERE clause, does not change col, so NO workey in SET clause.

You can try something like:

UPDATE Employees
SET HomePhone = '(222)' + RIGHT$(HomePhone, 5)
//SET HomePhone = CONCAT('(222)', RIGHT$(HomePhone, 5))
WHERE LEFT$(HomePhone, 5) = '(111)';

Tweak the syntax...

[Brenda1231]

Thanks qball.

I had meant to answer my own question since I figured it out after playing with it for awhile.
You were right, the correct code was:

SET HomePhone = '(222)' + RIGHT$(HomePhone, 5)
WHERE LEFT$(HomePhone, 5) = '(111)';


So I was doing as you stated witht he LEFT$, returning a value instead of replacing the value.

:rolleyes:

Now I am stuck on another problem...ahh such is life!