Re: Selected lines now how do I update?

David FOXWELL wrote:

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
INNER JOIN T
ON MyRefClient=OldCLIENT

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

<<SNIP proposed UPDATE>>

From what was given, there seems no obvious reason to have used a CTE. The UPDATE statement had referred to columns that are from MyTable1 versus the column MyRefClient in MyRefTable shown in the SELECT that was noted to return the rows desired for the update request. As such, I just snipped that from the quoted text. Maybe the following example which changes the values 'a', 'c', 'x' from MyRefClient to become 'A', 'C', 'E' respectively, might sufficiently mimic the scenario from which a statement might be understood to effect the desired outcome.

<code>

create table mytable1
(newclient char, oldclient char, sltind char)
;
insert into mytable1 values /* Upd if SltInd='1' */
('A', 'a', '1'), ('B', 'X', '0'), ('C', 'c', '1')
,('D', ' ', '0'), ('E', 'x', '1')
;
create table myreftable (myrefclient char)
;
insert into myreftable values
('a'),('b'),('c'),('d'),('e'),('f'),('x'),('y')
;
/* using the CTE to join with */
with t as (select newclient, oldclient from mytable1
where sltind='1')
select * from myreftable inner join T
on myrefclient=oldclient
;
/* move the WHERE into SELECT, eliminating CTE */
select * from myreftable inner join mytable1
on myrefclient=oldclient
where sltind='1'
;
update myreftable U
set U.myrefclient=
(select newclient from mytable1
where oldclient=U.myrefclient /* the join */
and sltind='1' /* the WHERE from the CTE */
)
where exists
(select newclient from mytable1
where oldclient=U.myrefclient /* the join */
and sltind='1' /* the WHERE from the CTE */
)
;
select * from myreftable
;
/* The lower case values a, c, x updated to
the upper case values A, C, E */
MYREFCLIENT
A
b
C
d
e
f
E
y
** End of data **

</code>

Regards, Chuck