RE: need help for a update command

John, Works a treat! Thanks for your rapid help. Regards, Malcolm. -----Original Message----- From: John Lynch (IS) [SMTP:John.Lynch@davy.ie] Sent: 15 May 2003 10:17 To: 'Malcolm Garbett' Subject: RE: need help for a update command Try this : update salesorders set sostate = "COMP" where sostate = "NEW" and sdate < "01/04/2000" and son IN (select unique sona from salesitems where soitemstate = "DONE" and sduedate < "01/04/2000"); Rgds, John. _______________________________________ John Lynch Information Services Davy Stockbrokers 49 Dawson Street Dublin 2 Tel: +353 1 614 8716 john.lynch@davy.ie -----Original Message----- From: Malcolm Garbett [ mailto:malcolm.g@newall.co.uk <mailto:malcolm.g@newall.co.uk> ] Sent: 15 May 2003 09:49 To: Informix-List (E-mail) Subject: Re: need help for a update command Hello, I was going to ask a similar question, so if I may join in... The problem I am trying to correct dates back to the early days of our system, when data was imported from an old system, and new users did not know how to correctly use the new ERP system/application. So...Sales orders that are complete have been left in the state "NEW". I know they are complete because the status in the related table salesitems has a status of "DONE". So...I tried this update salesorders set sostate = "COMP" where sostate = "NEW" and sdate < "01/04/2000" and son = (select unique sona from salesitems where soitemstate = "DONE" and sduedate < "01/04/2000"); This fails because of 284: A subquery has returned not exactly one row. Any help in pointing out the error of my ways greatly appreciated. Regards, Malcolm Garbett The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorised.if you are not the intended recipient, any disclosure, copying, distribution, or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. Please note that any views, opinions or advice contained in this email are those of the sending individual and not necessarily those of the firm. It is possible for data transmitted by e-mail to be deliberately or accidentally corrupted or intercepted. For this reason, where the communication is by e-mail, J&E Davy does not accept any responsibility for any breach of confidence which may arise from the use of this medium. If you have received this e-mail in error please notify us immediately at mailto:helpdesk@davy.ie and delete this e-mail from your system.