munster77054 wrote:

> I have these two tables, and the data shown in it (discussion continues below):
>
> CREATE TABLE CHANGEPARAMS
> (
> FIELD01 VARCHAR(30) NOT NULL,
> FIELD02 VARCHAR(30),
> PRIMARY KEY (FIELD01)
> );
>
> CREATE TABLE CHANGEME
> (
> FIELD01 VARCHAR(30) NOT NULL,
> PRIMARY KEY (FIELD01)
> );
>
> INSERT INTO CHANGEPARAMS (FIELD01, FIELD02) VALUES ('Fpdd', '@@');
>
> INSERT INTO CHANGEME (FIELD01) VALUES ('AjlMxjyqbeTI');
> INSERT INTO CHANGEME (FIELD01) VALUES ('hMhFWDTxqSZr');
> (...)

> What I'm trying to do is to replace all occurrences of the data in changeme (field01), with the value in changeparams field02 if the values in changeparams (field01) is contained in changeme (field01).

> Here is the most recent attempt:
>
> UPDATE CHANGEME
> SET CHANGEME.FIELD01 = REPLACE (CHANGEME.FIELD01,CHANGEPARAMS.FIELD01, CHANGEPARAMS.FIELD02)
> WHERE CHANGEME.FIELD01 CONTAINING CHANGEPARAMS.FIELD01
>
> This is the error I've been getting:
>
> Column does not belong to referenced table.
> Dynamic SQL Error.
> SQL error code = -206.
> Column unknown.
> CHANGEPARAMS.FIELD01.
> At line 3, column 49.

That's because the context is table CHANGEME, so CHANGEPARAMS.FIELD01 is interpreted as a column of CHANGEME - a column that obviously doesn't exist.

As long as table CHANGEPARAMS contains exactly one row, you can use subselects:

UPDATE CHANGEME
SET FIELD01 = REPLACE (FIELD01,
(SELECT FIELD01 FROM CHANGEPARAMS),
(SELECT FIELD02 FROM CHANGEPARAMS))

(Adding the WHERE... only gives the engine more work to do here; all records have to be scanned anyway.)

If there may be more records in CHANGEPARAMS, things get more complicated. An SP or executable block would then be the best way, I think, iterating over the search-replace pairs in CHANGEPARAMS and using the current values as parameters for UPDATE CHANGEME...


Cheers,
Paul Vinkenoog