7 Replies Latest reply on Sep 16, 2008 11:30 AM by -==cfSearching==-

    DB looping dilemma

    Balance
      First of all, as a rule of thumb, I try to avoid loops whenever possible. In this case, I'm not sure I have an option. I've been told to modify an import utility I built so that the system doesn't overwrite a field *IF* that field's import value is null. So, instead of having one nice UPDATE statement (update myTable FROM tempTable WHERE myTable.PK = tempTable.FK) which runs pretty fast and efficient, and now I'm having to do a nasty loop over the data-set for each contact and then checking each field and update that contact's field if it isn't blank. Can someone think of a better way to do this?
        • 1. Re: DB looping dilemma
          -==cfSearching==- Level 4
          I do not know how many columns you are updating, but what about using a CASE statement? You could structure it to use the tempTable value only if it is not null.

          SET myTable.someField =
          CASE
          --- when the import value is null, keep the existing value
          WHEN tempTable.someField IS NULL THEN myTable.someField
          --- otherwise, use the import table value
          ELSE tempTable.someField
          END
          • 2. Re: DB looping dilemma
            Dan Bracuk Level 5
            update mytable
            set field1 = (select field1 from mytemptable
            where mytemptable.primarykey = mytable.primarykey
            and field1 is not null)
            , field2 = (select field2 from mytemptable
            where mytemptable.primarykey = mytable.primarykey
            and field2 is not null)
            etc

            However, if your temp table has duplicate records of the primary key, this query will crash. If it's possible for the data you are importing to have duplicates, you have to do it one record at a time.
            • 3. DB looping dilemma
              -==cfSearching==- Level 4
              Dan Bracuk wrote:
              > update mytable
              > set field1 = (select field1 from mytemptable
              > where mytemptable.primarykey = mytable.primarykey
              > and field1 is not null)

              That would still overwrite the value in myTable when the import table value was null.

              • 4. Re: DB looping dilemma
                Dan Bracuk Level 5
                quote:

                Originally posted by: -==cfSearching==-
                Dan Bracuk wrote:
                > update mytable
                > set field1 = (select field1 from mytemptable
                > where mytemptable.primarykey = mytable.primarykey
                > and field1 is not null)

                That would still overwrite the value in myTable when the import table value was null.



                I don't think so. You can try it if you want. You can also qualify field1 in the subquery.
                • 5. Re: DB looping dilemma
                  -==cfSearching==- Level 4
                  Dan Bracuk wrote:
                  > I don't think so. You can try it if you want. You can also qualify field1 in the subquery.

                  Yes, I tried it and it does set overwrite the value. At least on ms sql.


                  • 6. Re: DB looping dilemma
                    editcorp Level 1
                    If you don't mind replacing the existing value with itself (you still end up doing the update regardless, but the value would remain the same if the import value is null), you can do something like (in MSSQL):

                    Update MyTable
                    set myColumn = isNull(importField,myColumn)

                    -or-
                    Update MyTable
                    set myColumn = case when importField is NULL then myColumn else importField end
                    • 7. Re: DB looping dilemma
                      -==cfSearching==- Level 4
                      editcorp wrote:
                      > If you don't mind replacing the existing value with itself (you still end up doing the
                      > update regardless, but the value would remain the same if the import value is null),

                      Yes, that is what I was suggesting above. I think that would be simpler.