Re: Selected lines now how do I update?

[Jim Steil <jim@xxxxxxx>]

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.
>