4

I am having a problem with syntax. I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the text in field 142 unless field 142 is null, then update with field 17. Here is the existing code:

--Defendant Address
update xrji_:Usernum 
    set DefendantAddress=trim(MemoText)
from eqanswer
where eqanswer.entitynum=xrji_:Usernum.DefendantEntityNum and
                       entityrole='DEFENDANT' and fieldnum=142
; 

Columns MemoText, entityrole and fieldnum belong to the eqanswer table.

Let me know if this needs further clarification. Any suggestions would be appreciated.

DBMS is: Advantage.

4

The principal difficulty here is probably that the alternative value of MemoText you want to use in case the fieldnum=142 value is null is in a different row. It would be much easier if it was just a different column of the same row. That way you could use the pattern suggested by Manu – I mean specifically this part:

set <field_to_update> = ISNULL(<field142>, <field17>)

The ISNULL function will evaluate to <field142> if that value is not null, otherwise the function will evaluate to <field17>.

So is it possible to arrange two values from different rows on the same row? Yes, there are at least two ways to do so.

First, you could replace the eqanswer reference with a derived table that groups eqanswer rows by entitynum and uses conditional aggregation to present field 142 and field 17 values on the same row, like this:

SELECT
  entitynum,
  MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
  MAX(CASE fieldnum WHEN 17  THEN MemoText END) AS MemoText17
FROM
  eqanswer
WHERE
  entityrole = 'DEFENDANT'
  AND fieldnum IN (142, 17)
GROUP BY
  entitynum

Now you can use the above query in the UPDATE statement like this:

UPDATE
  xrji_:Usernum
SET
  DefendantAddress = TRIM(ISNULL(eqa.MemoText142, eqa.MemoText17))
FROM
  (
    SELECT
      entitynum,
      MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
      MAX(CASE fieldnum WHEN 17  THEN MemoText END) AS MemoText17
    FROM
      eqanswer
    WHERE
      entityrole = 'DEFENDANT'
      AND fieldnum IN (142, 17)
    GROUP BY
      entitynum
  ) AS eqa
WHERE
  eqa.entitynum = xrji_:Usernum.DefendantEntityNum
;

The other method would require joining the eqanswer table to xrji_:Usernum twice: once to get the field 142 MemoText and once again to get the field 17 MemoText. For this it would be more convenient to rewrite UPDATE to use an explicit join. The complete statement would look something like this:

UPDATE
  xun
SET
  DefendantAddress = TRIM(ISNULL(eqa142.MemoText, eqa17.MemoText))
FROM
  xrji_:Usernum AS xun
  LEFT JOIN eqanswer AS eqa142 ON eqa142.entitynum = xun.DefendantEntityNum
    AND eqa142.entityrole = 'DEFENDANT'
    AND eqa142.fieldnum = 142
  LEFT JOIN eqanswer AS eqa17 ON eqa17.entitynum = xun.DefendantEntityNum
    AND eqa17.entityrole = 'DEFENDANT'
    AND eqa17.fieldnum = 17
WHERE
  eqa142.entitynum IS NOT NULL
  OR eqa17.entitynum IS NOT NULL
;

You can see that this UPDATE statement uses the same ISNULL pattern as the previous UPDATE does. The difference is only in how the MemoText values from different rows manage to get on the same row. This time they simply come from different, independently joined subsets of eqanswer.

The query uses outer joins instead of inner joins because of the assumption that field 142 or field 17 may be absent for any given entitynum. The query still checks that at least one of the two rows is present – otherwise the corresponding xrji_:Usernum row is not updated at all. If you still would like to update such rows (with a null, apparently), just remove the WHERE clause.

1
update xrji_:Usernum 
    set DefendantAddress = 
        trim(CASE 
             WHEN MemoText IS NULL OR MemoText = ''
             THEN <'text data from field 17'>
             ELSE MemoText
             END)
from eqanswer
where eqanswer.entitynum = xrji_:Usernum.DefendantEntityNum 
    and entityrole = 'DEFENDANT'
    and fieldnum = (CASE 
                    WHEN 142 IS NULL OR 142 = ''
                    THEN 17 
                    ELSE 142 
                    END);

I hope this can get you closer to your answer :)

  • 1
    What the OP needs is not very clear to me. But yours and Manu's answer differ. In yours the CASE expression is in the where clause. In Manu's it's in the SET. They'll do different things. – ypercubeᵀᴹ Apr 26 '16 at 6:38
  • 1
    Thank you all for your help. I need to set the field DefendantAddress to whatever is in field 142, unless 142 is blank (not populated) in that case I need to set the field DefendantAddress to whatever is in field 17. JMaurier, this is close, It appears to be updating the field to the contents of 142 whether it is populated or not. Any thoughts? Thanks again. – R. Beck Apr 26 '16 at 15:43
  • I see, what is the column name for field 17? in field 142 it's called MemoText. If it is different then I think you would need to put the same type of CASE WHEN statement in your trim. Also (this may seem trivial) is the text NULL or just an empty string? – Jmaurier Apr 27 '16 at 3:16
  • 1
    Jmaurier, I think you hit the problem with the null vs. empty string. I believe the field is an empty string, not a null field. Also, I will try the field name suggestion as well. Thanks again for your help. – R. Beck Apr 27 '16 at 11:46
  • Jmaurier - Thanks for the response, just trying to understand what you have provided. What will the trim (CASE WHEN... statement do for us here and can you provide any help with he syntax for the THEN <'text data from field 17'> statement. I am sure it is obvious but I am a novice here so any help is greatly appreciated. Thank you. – R. Beck Apr 27 '16 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.