Re: Query - REPLACE using an INNER JOIN

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

angela.y.austin@xxxxxxxxx wrote:

I'm running the query below to replace one letter.

I run

SELECT replace(ePPA_No, 'n', 'Y')
FROM medman_purge INNER JOIN
in4_purge ON medman_purge.Col001 = in4_purge.eppa_no

with no problem, and it replaces the 'N' with 'Y' perfectly. When I
try to set it using this query:

update in4_purge
set eppa_no =
(SELECT replace(ePPA_No, 'n', 'Y')
FROM medman_purge INNER JOIN
in4_purge ON medman_purge.Col001 = in4_purge.eppa_no)

I get this error:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.

What am I doing wrong?

At least one row in in4_purge has two or more associated rows
in medman_purge. Try the following:

update in4_purge
set eppa_no = replace(eppa_no, 'n', 'Y')
where eppa_no in (select Col001 from medman_purge)
.