Sql update table column with value from another table 1

[cjkoontz]

Can't seem to find the method for doing this. I've got this ibm reference --

http://as400bks.rochester.ibm.com/cgi-bin/bookmgr/BOOKS/QB3AQ902/CCONTENTS

and have searched in vain for the following --

How to update a table's column with values from another table.

That is, for each record where table x's colx1 = table y's coly1, update table x's colx2 with table y's coly2.

Do-able with SQL/400?

 

[Pekka]

How about this?
UPDATE x SET colx2 =
(Select coly2
From x A, y B
where a.colx1=B.coly1
)

 

[cjkoontz]

Thanks for the reply.

I've tried that example before, and have gotten from iSeries STRSQL the following message --

"Result of SELECT more than one row".

 

[cjkoontz]

Turns-out this statement will do what I want it to do. Sort of.

UPDATE x A SET A.colx2 =
(Select B.coly2
From y B
where A.colx1=B.coly1)

It works a part of the way through and I get the following message:

"Null values not allowed in column or variable colx2"

This is causing much confusion because I can't find this null value that STRSQL is claiming is out there!

 

[iSeriesCodePoet]

If, in your select, there is data in one table and not the other, you will get a null value. If you used something like: COALESCE( A.CATSALES, 0.00 ) where 0.00 is the value you want to replace null with. This will fix it. So your SQL would look like:

UPDATE x A SET A.colx2 =
(Select COALESCE( B.coly2, ' ')
From y B
where A.colx1=B.coly1)

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[cjkoontz]

Dear iSeriesCodePoet,


That did the trick!

One interesting side note, though, the table I wanted to update-from has only 7,000 records.

Yet, after the UPDATE statement ran, STRSQL issued the following message --

60785 rows updated in x in xlibrary.

Why does STRSQL say it has updated more records than was in the update-from file?

 

[iSeriesCodePoet]

How many records where in the file you where updating? I did you do a select before you updated to make sure there wasn't any problems?

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[cjkoontz]

RE> "How many records where in the file you where updating?"
A- 60785. The same number that STRSQL said it updated.

RE> "...did you do a select before you updated to make sure there wasn't any problems?"

Not sure what you mean.

 

[iSeriesCodePoet]

If you do:

SELECT x A where A.colx2 =
(Select COALESCE( B.coly2, ' ')
From y B
where A.colx1=B.coly1)

Then you can verify your update before you run the update. It will elminate any potential problems.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[PeteJohnston]

I think that if WHERE A.colx1 = B.coly1 gives a one-to-many relationship rather than a one-to-one, SQL thinks it has done an update for each. If one record in table X matches 3 records in table Y, SQL will tell you that 3 updates have been performed even though only one record has been updated.

Where it would worry me is not knowing which field values were used to update the field on table X. It could be any one of the three matching records from table Y.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[cjkoontz]

By the way, I got a timely bit of info from the --
ClubTech Program Tips newsletter.
( ClubTechProgramTips_VIPPro@lists.pentontech.com )

Julian Moneypenny provided these SQL UPDATE examples, using invbal and invmst as the example tables --

To set the department to zero when no match is found, you use the following:

update invbal set
dept = (select coalesce( invmst.dept, 0 ) from invmst
where invmst.item = invbal.item)

To leave the department as is when no match is found, you use this:

update invbal set
dept = (select coalesce( invmst.dept, invbal.dept ) from invmst
where invmst.item = invbal.item)

You can use a single update statement to update more than one column. To do this, each column's value must be set to a subselect. So, to update both the DEPT and CLASS columns in one go, you would specify the following:

update invbal set
dept = (select coalesce( invmst.dept, 0 ) from invmst
where invmst.item = invbal.item),
class = (select coalesce( invmst.class, 0 ) from invmst
where invmst.item = invbal.item)