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
As an Amazon Associate we earn from qualifying purchases.