Actually, COALESCE can take more than two arguments, but in this case only 2 are neded.

Just a couple of observations:

-If no row exists in VDA_CS that matches the “A.B1LWNB = B.B1LWNB” condition, COALESCE will not help, as there will be no result set to apply the function to.
-The UPDATE command doesn’t have a WHERE clause, so it will try to update all rows in ACTPHY01, even if they don’t have a matching row in VDA_CS.

Okay, since I am new to this I will explain the situtation a little better. I think Carlosdi caught my problem but I still am not sure what my statement should look like The field I am updating is a 3 byte alpha. I have maybe 100,000 records in file A and 300 records in file B. I only wish to update the 300 records in file A with the field that is in file B. So does that mean I need a second where clause or did my where clause need to be outside the (_) .i tried a few more combos… all have failed.
Thanks for both of your help. Based on what I have mentioned above how shouid this be coded?

Do you need to update table A with the value of ALL records in table B? If so, I recommend you to add a WHERE clause to the UPDATE statement to verify if the record exists in the table B (EXISTS clause).

This way the inner SELECT will return all the records, and the UPDATE statement will be conditioned by the existing values of table B for the corresponding records due to EXISTS clause.

UPDATE DABCON2/ACTPHY01 A SET A.B1N4CD = (SELECT COALESCE(B.B1N4CD,0) FROM DABCON2/VDA_CS B WHERE A.B1LWNB = B.B1LWNB) WHERE EXISTS (SELECT 1 FROM DABCON2/VDA_CS WHERE B1LWNB = A. B1N4CD)

I made a mistake in the previous message.
The WHERE clause for UPDATE statement must be like this

WHERE EXISTS
(SELECT 1 FROM DABCON2/VDA_CS WHERE B1LWNB = A.B1LWNB)

Regards.

I have had a similar situation but I wanted to replace a blank value in file 2 with some text to say a value was not present. The update statement shown worked but used the value from file 2 instead of the replacement value in the coalesce.

Update jsherryb/Cosrexp a
Set a.Corptg = (Select coalesce(b.RPGRGP,’No Group Sent from IP’)
from jsherryb/RpgpYYMMDD b where a.colinb = b.rpglin)
Where Exists (Select 1 from jsherryb/RpgpYYMMDD where rpglin = a.colinb)

Do I need to use a CASE statement in the coalesce to achieve this…any help would be appreciated

Jcfsb, which one is “file 2″ in this case ?

I ask because you said that you wanted to “replace a blank value in file 2…”, and since the file being updated is Cosrexp, I’d say that file 2 is Cosrexp.

But then you said that it “worked but used the value from file 2 instead of the replacement value in the coalesce”, which seems to imply that file 2 is RpgpYYMMDD, since that is the file where the new value is coming from in your query.

Could you please clarify ?

Also…

I wanted to replace a blank value in file 2…

I don’t see anywhere that it is testing for a blank.

…to say a value was not present.

A “blank” is not the same as “a value was not present”. Are you wanting to work with blanks? Or are you actually trying to handle nulls (which are very different from blanks.)

Tom

Situation here is very similar to Dab77 and I was on tract with Mariodlg post.
I want to update a filed in file A with content from file B. File A has more records thatn file B. The statement here still returns a “Null values not allowed . . . ”

Can anyone shed some light for me, the green horn of sql?

UPDATE VINITMB A
SET (a.IFOTH) = (SELECT coalesce (B.IGCOST,0)
FROM ITEMVND1 B
WHERE A.IFITEM = B.IGITEM AND A.ifloc = B.IGLOC AND A.IFDIV = ’116′)
WHERE EXISTS
(SELECT 1 FROM ITEMVND1 B where b.IgITEM = a.IfITEM AND b.igloc = A.IFLOC)

UPDATE VINITMB A
SET IFOTH = (SELECT coalesce (B.IGCOST,0)
FROM ITEMVND1 B WHERE A.IFITEM = B.IGITEM AND A.IFLOC = 1
AND A.IFDIV = ’116′)
WHERE EXISTS
(SELECT 1 FROM ITEMVND1 B where B.IGITEM = A.IFITEM AND B.IGLOC = A.IFLOC)

your where exists select and your update select have different criteria. The update select is returning fewer result sets.

UPDATE VINITMB A
SET IFOTH = (SELECT coalesce (B.IGCOST,0)
FROM ITEMVND1 B WHERE A.IFITEM = B.IGITEM AND A.IFLOC = 1
AND A.IFDIV = ‘116′)
WHERE EXISTS
(SELECT 1 FROM ITEMVND1 B where B.IGITEM = A.IFITEM AND B.IGLOC = A.IFLOC)

it isn’t exactly clear that I bolded the line
AND A.IFDIV = ‘116′
in my reply

UPDATE VINITMB A
SET (a.IFOTH) = (SELECT coalesce (B.IGCOST,0)
FROM ITEMVND1 B
WHERE A.IFITEM = B.IGITEM AND A.ifloc = B.IGLOC AND A.IFDIV = ‘116′)
WHERE EXISTS
(SELECT 1 FROM ITEMVND1 B where b.IgITEM = a.IfITEM AND b.igloc = A.IFLOC)

The UPDATE needs two WHERE clauses.

The first WHERE clause needed will be the one that determines which rows to update. For example:

UPDATE VINITMB A
SET (a.IFOTH) = 'NewVal'
WHERE EXISTS
(SELECT 1 FROM ITEMVND1 B where b.IgITEM = a.IfITEM AND b.igloc = A.IFLOC)

I replaced the subselect with a basic value assignment. I have no idea if it’s a valid value; it merely represents the concept.

The WHERE clause should now be limiting the updated rows only to the rows you need. For this, you could use a SELECT instead of an UPDATE:

SELECT * FROM VINITMB A
WHERE EXISTS
(SELECT 1 FROM ITEMVND1 B where b.IgITEM = a.IfITEM AND b.igloc = A.IFLOC)

The selected rows will show you which ones would be updated if you put that WHERE clause on the UPDATE. If the wrong rows are listed, then keep modifying the WHERE clause until the right result set is defined.

Once that works, you can work on the next WHERE clause.

That one needs to return exactly one value for each row in the result set. There should be no null values returned. Again you could use a SELECT to verify that conditions will work. Maybe something like:

SELECT
 A.IFITEM,
 A.ifloc,
 A.IFDIV,
 a.IFOTH,
 (SELECT coalesce (B.IGCOST,0)
   FROM ITEMVND1 B
   WHERE A.IFITEM = B.IGITEM AND A.ifloc = B.IGLOC AND A.IFDIV = ‘116′) as MyCost
 FROM VINITMB A
 WHERE EXISTS
  (SELECT 1 FROM ITEMVND1 B where b.IgITEM = a.IfITEM AND b.igloc = A.IFLOC)

I don’t know how many unique values there are in your files nor how they match up between your files, so I don’t know what will work. But I do know that you can’t have a successful update until you narrow both WHERE clauses down to give correct results.

It’s a lot safer to test with SELECT, and results are far more visible.

Tom

Okay, I know it’s been a long time but back at this issue.
Here is my working select statement. These are the items to be updated. FYI the second file name has change from the previous threads.
SELECT * FROM VINITMB A                                         WHERE EXISTS                                                    (SELECT 1 FROM vinitvn B where b.IgITEM = a.IfITEM AND a.ifloc =’3′ AND a.ifprvn = b.igvnd AND a.ifdiv = 120)           
The tie breaker on duplicate results is the a.ifprvn = b.igvnd. This makes the unique selection.
The second select is the one nested inside correct? The one that uses ’COALESCE’, right?   Now, what exactly does the “second” select statement do?   

Figured it out!  criteria needed to be the same for both where.