Solved

How to update null field with SQL?

Posted on 2000-04-18
3
153 Views
Last Modified: 2010-04-04
This update function works fine for non-null values, but it won't work if the field requests is null.  How do I fix it?

function IncRequests(trkid : integer) : boolean;
var ok : boolean;
     q : TQuery;
begin
  ok := false;
  q := TQuery.Create(nil);
  try
    if trkid > 0 then
      begin
        q.SQL.Clear;
        q.SQL.Add('update tracks.db set requests = requests + 1 '+
                  'where id = '+inttostr(trkid));
        q.ExecSQL;
        ok := true;
      end;
  finally
    q.Free;
  end;
  IncRequests := ok;
end;
0
Comment
Question by:rwv
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
simonet earned 100 total points
ID: 2729303
There are 2 ways to solve that:

1) Before calling the routine, make sure there are no NULL values for "Requests". You can do that, by changing all nulls to zero, so your routine works:

UPDATE "tracks.db" SET Requests=0 WHERE Requests IS NULL

2) Prevent your existing routine to work on records where "Requests" has a null value. You can do that by adding an extra test to the existing WHERE clause:

  q.SQL.Add('update tracks.db set requests = requests + 1 '+
                  'where id = )'+inttostr(trkid)+') AND (Requests IS NOT NULL)');


You choose whichever way works best for your specific needs.

Yours,

Alex
0
 

Author Comment

by:rwv
ID: 2729357
Alex,
Is it possible to use an if then statement in sql?
ie -
if requests is null then requests = 1
else requests = requests + 1

Bobby
0
 
LVL 15

Expert Comment

by:simonet
ID: 2729388
Unfortunatly not, Bobby. I also wish it was possible, but so far, it's not.

Alex
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question