CODEWITHSUNDEEP
sqlinformix
Ben
Ben

Reputation: 669

Updating a field from other rows of the same table in Informix

We have an old part number system that lead to a number of duplicate records. We've since created a new system that should prevent that, and I'm now trying to tie the old part numbers to the current equivalent. The program we were using for this function initially only allowed for manually linking one old part number to the new part number; if there were 5 duplicates, each one would need to be manually linked. I'm attempting to write an update query to link all these duplicate old parts to the single new part that is replacing it.

Here is what the data looks like, as an example, with one old part linked to the new part:

Old Part       New Part
123            XYZ
123
123
123
123

What I need to do is to update rows 2-5 so that they each say XYZ in the new part field.

Any suggestions on how to do this? Here's a couple of queries I've tried:

1

update invmas dt1, invmas dt2
set dt1.inv_new_code = dt2.inv_new_code
where dt1.inv_stock_code = dt1.inv_stock_code and dt2.inv_new_code is not null

2

update invmas as t1
inner join invmas as t2 on
t1.inv_stock_code = t2.inv_stock_code and t2.inv_new_code is not null
set t1.inv_new_code = t2.inv_new_code

Unfortunately they're both generating syntax errors and I'm not having much luck figuring out an alternative that works.

Upvotes: 0

Views: 796

Answers (2)

RET
RET

Reputation: 9188

An UPDATE involving a self-join may not be permitted. Try breaking the job into two steps, eg untested:

SELECT DISTINCT inv_stock_code, inv_new_code
  FROM invmas
  WHERE inv_new_code IS NOT NULL
INTO TEMP mlist1 WITH NO LOG;

UPDATE invmas AS i
  SET inv_new_code = (SELECT m.inv_new_code
                      FROM mlist1 AS m
                      WHERE i.inv_stock_code = m.inv_stock_code)
  WHERE inv_new_code IS NULL;

Note: this will still explode if you have multiple new codes for a single old code, but from your description of the problem that's something you'd need to correct anyway.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269963

The following does not seem to work, but I'm leaving this answer to help others:

update t1
    set t1.inv_new_code = t2.inv_new_code
from invmas t1 inner join
     invmas t2
     on t1.inv_stock_code = t2.inv_stock_code and t2.inv_new_code is not null

Upvotes: 0

Related Questions