Re: SQL Update

bkersey@wolsi.com wrote: :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) You have to do a select from stock in the set part. Something like this: update holstk set holstk.namt = ((select stock.c_prc from holstk, stock where holstk.pc = stock.pc)) where holstk.pc in (select stock.namt from holstk, stock -- <- and include the holstk table here where holstk.pc=stock.pc) I do however not understand how holstk.pc can be in stock.namt when the where clause contains holstk.pc = stock.pc. Does the pc and namt columns of stock contain exactly the same thing? One guess may be that what you realy want from this last where clause is: where holstk.pc in (select stock.pc from stock) but I may be very wrong. Do remember that you need a where clause both on the select in the set part and on the update itself. It may be tempting to remove the where clause on the update, but then every row of holstk would be updated whether the select in the set part found any rows or not. Before you do anything more you should find the explanations on these kinds of updates in the manual and read them carefully. The above is typed in from memory only. I am not even quite sure if the double paranteses where realy needed here, but there was an error in some old manuals on just that issue. :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 Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org