Re: Selected lines now how do I update?

You should be able to do this without the CTE

UPDATE MyRefTable r
SET r.MyRefClient = (select NewClient from MyTable1 t where r.MyRefClient
= t.OldClient)
WHERE r.MyRefClient in (
SELECT a.MyRefClient
FROM MyRefTable a
JOIN MyTable1 b ON a.MyRefClient = b.OldClient
WHERE etc, etc)

Notice that the in clause is referencing a set with one column. There is
no field name matching here.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx



From: David FOXWELL <David.FOXWELL@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 05/03/2010 06:07 AM
Subject: Selected lines now how do I update?
Sent by: midrange-l-bounces@xxxxxxxxxxxx



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.