SQL Update not Working using two files

80 pts.
Tags:
AS/400
AS/400 SQL
SQL
SQL Server
SQL Server Updates
I have attempted to update one file with data from another but I am getting two different errors depending on how I enter my statements.

I used information from this site as a reference for these statements but I must be doing something wrong.

UPDATE DABCON2/ACTPHY01 A SET A.B1N4CD = (SELECT B.B1N4CD FROM DABCON2/VDA_CS B WHERE A.B1LWNB = B.B1LWNB)               [em]Null values not allowed in column or variable B1N4CD. [/em]

(2nd attempt trying to handle null value)

UPDATE DABCON2/ACTPHY01 A SET A.B1N4CD = COALESCE(SELECT B.B1N4CD FROM DABCON2/VDA_CS B WHERE A.B1LWNB = B.B1LWNB) [em]Token B was not valid. Valid tokens: ) ,.[/em]

Any help will be appreciated!

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think you’ve figured out that the ACTPHY01 table doesn’t accept nulls for B1N4CD field, and that is the reason why you tried to use “coalesce” function in the second command, is that correct?

I think you are using COALESCE in wrong way, because COALESCE uses 2 parameters, the first one is for the field that you know has null values, and the second one is for the value that will be returned everytime that a null value in found in the specified field.

In other words you must specify new value to replace nulls from the orginal table using COALESCE function, like this:

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

I’m assuming that B.B1N4CD field is a number, change the zero value in the second parameter according to your needs.

Tell us if this works for you.

Regards.

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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.
    69,240 pointsBadges:
    report
  • dab777
    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?
    80 pointsBadges:
    report
  • Mariodlg
    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)
    2,790 pointsBadges:
    report
  • Mariodlg
    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.
    2,790 pointsBadges:
    report
  • Jcfsb
    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
    15 pointsBadges:
    report
  • carlosdl
    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 ?
    69,240 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • rsnc
    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)
    30 pointsBadges:
    report
  • BigKat
    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)
    8,100 pointsBadges:
    report
  • BigKat
    it isn't exactly clear that I bolded the line AND A.IFDIV = ‘116′ in my reply
    8,100 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • rsnc
    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?   
    30 pointsBadges:
    report
  • rsnc
    Figured it out!  criteria needed to be the same for both where.
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following