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?

[-__cfSearching__- · Accepted solution]

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

[Dan_Bracuk]

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.

[-__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.

[Dan_Bracuk]

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.

[-__cfSearching__-]

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.

[editcorp]

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

[-__cfSearching__-]

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.