Re: SQL Update
>From: bkersey@wolsi.com
>Date: Tue, 02 Sep 1997 21:51:30 -0600
>X-Informix-List-Id: <news.42417>
>
>Can someone please tell me what is wrong with this:
>update holstk
>set holstk.namt = (stock.c_prc)
>where holstk.pc in
>(select stock.namt
> from stock
> where stock.pc=holstk.pc)
>
>The reported error is:
> Table does not exists
> Table: stock
>
>Well Stock sure does as this work :
>Select stock.pc, holstk.pc
>from stock, holstk
>where stock.pc=holstk.pc
The problem is the reference to stock.c_prc on the RHS of the SET clause.
You actually need to write:
UPDATE holstk
SET namt = ((SELECT stock.c_prc FROM stock WHERE stock.pc = holstk.pc))
WHERE pc IN
(SELECT stock.namt -- ?? stock.pc ??
FROM stock
WHERE stock.pc=holstk.pc)
Yes, the double parentheses are necessary. No, I've not run this to double
check that it works. And I'm assuming that the sub-query in the UPDATE
statement is OK, though I'm rather sceptical about that. I think that
maybe it should read:
WHERE pc IN (SELECT pc FROM stock)
This should be quicker as it eliminates one of the two correlated
sub-queries, as well as being possibly more accurate (because it compares
holstk.pc with stock.pc rather than stock.namt).
Do not eliminate the WHERE clause in the UPDATE because you will run into
problems setting the holstk.namt column to NULL for any row where the
sub-query in the SET clause does not return a value.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
PS: Warning I do not reply to messages with anti-spam in the return path.