> update dzhhq set mnote=
> case when mnote in (select mnote from dzhhq inner join (select bs,stkcode
> from buysell) b on dzhhq.stkcode=b.stkcode) then 'sell'||mnote
> else mnote
> end
> is ok, but when I use this cmd then appear error msg:
> update dzhhq set mnote=
> case when mnote in (select mnote from dzhhq inner join (select bs,stkcode
> from buysell) b on dzhhq.stkcode=b.stkcode and (dzhhq.qph60<buysell.qph60)
> and (buysell.bs="sell")) then 'sell'||mnote
> else mnote
> end
Buysell.qph60, buysell.bs, and "sell" are not visible columns. You don't
include the actual error message. " and ' quote different things. ' quotes
string values and " quotes names (database/table/column/index &c).
update dzhhq set mnote=
case when mnote in (select mnote from dzhhq, buysell where
dzhhq.stkcode=buysell.stkcode and dzhhq.qph60<buysell.qph60
and buysell.bs='sell') then 'sell'||mnote else mnote end
is a valid statement.
I suspect
update dzhhq
set mnote='sell' || mnote
where exists (select *
from buysell
where dzhhq.stkcode = buysell.stkcode
and dzhhq.qph60 < buysell.qph60
and buysell.bs = 'sell')
is a more succinct expression of what you actually want to accomplish. You
will also only update the rows that need updating rather than every row. If
you have the proper index on the buysell the correlated subquery will be
satisfied entirely from the index. Whether that is important depends on the
size of the buysell table, of course.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
