DB looping dilemma

Forum|Forum|17 years ago
September 16, 2008
4 replies
691 views

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?

Advanced techniques

This topic has been closed for replies.

Correct answer -__cfSearching__-

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

E

editcorp

Inspiring

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

Inspiring

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.

-

-__cfSearching__-

Inspiring

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.

D

Dan_Bracuk

Inspiring

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

Inspiring

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.

D

Dan_Bracuk

Inspiring

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__-Correct answer

Inspiring

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded