Re: need help for a update command
Unique key to join the salesorders to the sona table?
i.e. provide a dbschema of the two tables, and give us a clue as to how
they join because :
select unique sona from salesitems where soitemstate = "DONE" andsduedate < "01/04/2000";
will provide ALL the rows, that satisfy the two where clauses.
You could try
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" and son = sona);
or
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" and son = sona);
but it is hard to tell without some "sample data", or an idea of the
relationships between the two tables.
Spudilka
Malcolm Garbett wrote:
> 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