Re: Selected lines now how do I update?

I'm not really well-versed in this WITH clause, but it seems to me that your returning multiple values in your final SELECT statement, but your WHERE clause is looking to match the IN predicate with only a single value. Instead of using 'SELECT *' in the final statement, I'd try specifying the specific field name you are looking for.

But again, I don't claim divine knowledge here.

-Jim

On 5/3/2010 5:01 AM, David FOXWELL wrote:

Hi,

I'm having a problem getting an UPDATE right after having selected the records to update.

Here's my select :

WITH T AS (

SELECT
NewCLIENT, OldCLIENT,
FROM MyTable1 WHERE etc, etc )

SELECT * FROM MyRefTable JOIN T ON
MyRefclient=OldCLIENT




Now I want to replace the old client with the new, but this doesn't seem to be right :

UPDATE MyRefTable
SET OldCLIENT = NewCLIENT WHERE OldClient in (

WITH T AS (

SELECT
NewCLIENT, OldCLIENT,
FROM MyTable1 WHERE etc, etc )

SELECT * FROM MyRefTable JOIN T ON
MyRefclient=OldCLIENT )


Thanks for any help.