Board index » delphi » dBase and UPDATE SQL help needed

dBase and UPDATE SQL help needed

I'm trying to write a SQL statement that will update NULL fields to 0
for use with some SQL aggregate functions. Unfortunately, I have only
been able to update 1 column at a time:

UPDATE [TableName]
SET [ThisField] = 0
WHERE [ThisField] IS NULL

This is fine for a single field check...however I have 30 of these to
update! Is there a faster way to do this in SQL?

These are FoxPro tables (using .CDX indexes).

I am using Delphi 3.01 and BDE 4.x (for Apollo reasons...)

Any help is appreciated!

Bill Artemik
Programmer / Analyst
Droste Consultants, Inc.
b...@droste1.com

"There are THREE kinds of people in the world...
      Those that can count and those that can't."

 

Re:dBase and UPDATE SQL help needed


On 3 Sep 1999 08:14:07 -0500, b...@droste1.com (Bill Artemik) wrote:

Quote
>I'm trying to write a SQL statement that will update NULL fields to 0
>for use with some SQL aggregate functions. Unfortunately, I have only
>been able to update 1 column at a time:

>UPDATE [TableName]
>SET [ThisField] = 0
>WHERE [ThisField] IS NULL

>This is fine for a single field check...however I have 30 of these to
>update! Is there a faster way to do this in SQL?

The UPDATE statement allows you to update as many target columns as you
wish. Specify multiple target columns in an UPDATE statement by providing a
comma-separated list of column assignments in the SET clause. For example:

  UPDATE SomeTable
  SET Col1 = 0, Col2 = 0, Col3 = 0, ...

However, only one WHERE clause is allowed. And I think this is where your
difficulty lies. In your scenario, when you are updating ThisField you only
want the UPDATE to affect rows where ThisField is NULL. The setting of
other fields' values cannot necessarily be done using this same WHERE
clause criteria and criteria based on other columns cannot really be used
for deciding when to update ThisField. What you really need, then, is what
you see you need: multiple update statements. Unless all the columns to be
updated will be NULL when ThisField is NULL, you are going to need to issue
one UPDATE statement for each combination of column to update and column in
the WHERE clause.

One way to aproach this would be to execute the TQuery in a loop. Within
this loop, rewrite the SET and WHERE clause lines of the UPDATE statement.
The TQuery.SQL property is a string list object, an array of strings. So
you can address an individual lines and set its value independent of the
other strings in the array. For example:

  for i := 1 to NumberOfFieldsToUpdate do begin
    ... set NameOfFieldVar value ...
    Query1.SQL[1] := 'SET ' + NameOfFieldVar + ' = 0';
    Query1.SQL[2] := WHERE ' + NameOfFieldVar + ' IS NULL';
    Query1.ExecSQL;
  end;

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx

Other Threads