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