Paradox Community

Items in pnews.paradox-programming

Subject:Re: LocalSQL update - avoid nulls works but
Date:4 Jun 2020 00:00:49 -0400
From:"Kevin Zawicki" <numberjack@wi.rr.com>
Newsgroups:pnews.paradox-programming

I did this
SQL
   UPDATE "tbl1" 1
   SET 1.Qty = 1.Qty +
   (
   SELECT AN.Qty
   FROM ":PRIV:Answer.db" AN
   WHERE AN.ID = 1.ID
   )
   WHERE 1.ID IN (SELECT ID FROM ":PRIV:Answer.db")
  endSQL


I added 
   WHERE 1.ID IN (SELECT ID FROM ":PRIV:Answer.db")
to restrict updates in tbl1 to ID in answer.


Any feedback on better way to do this?


"Kevin Zawicki" <numberjack@wi.rr.com> wrote:
>
>
>I have two tables, one contains a list of item#s and a QTY field.
>
>The second is an answer table that may only have some of the items and a
>qty.
>
>I need to update the qty in table 1 by adding whatever qty in table 2 to
>the qty in table 1.
>
>SQL
>   UPDATE "tbl1" 1
>   SET 1.Qty = 1.Qty +
>   (
>   SELECT AN.Qty
>   FROM ":PRIV:Answer.db" AN
>   WHERE AN.ID = 1.ID
>   )
>  endSQL
>
>What this seems to be doing if in answer it adds qty to tbl1, but if not
>in answer it changes tbl1 qty to blank.
>
>Must be missing something easy, a join?
>
>Basically I need to:
>if ID in answer and in tbl1, change the tbl1 qty to the sum of it and what
>is in answer.


Copyright © 2004 thedbcommunity.com