> update myref set upfu =dzhhq.upfu where exists (select * from dzhhq where
> myref.stkname=dzhhq.stkname);
> but it said no such column: dzhhq.upfu
> but dzhhq.upfu really exist.
No, it does not. The cursor is the updateable table myref only. You can set a
column in myref to be any computable expression. Dzhhq is not a table visible
in that cursor - it is accessible only within the correlated subquery, but not
in the update cursor. You can access "outer" tables in correlated subqueries,
but "inner" tables are only visible within the subquery and subqueries
contained therein.
Update myref
Set upfu = (select dzhhq.upfu
from dzhhq
where dzhhq.stkname = myref.stkname)
where (select count(*)
from dzhhq
where dzhhq.stkname = myref.stkname) = 1
and (select dzhhq.upfu
from dzhhq
where dzhhq.stkname = myref.stkname) <> myref.upfu;
which translates to
for each row in myref where there is one and only one row with the same stkname
value in table dzhhq (this is the subset of all rows in myref which are
updateable) and where the value needs updating
set myref.upfu to the to the value of upfu plucked from dzhhq where the stkname
fields are equal
This uses three correlated subqueries -- one of which determines the rows in
myref suitable for updating (that have one and only one matching stkname in
dzhhq) , one which plucks the value to determine if the value needs updating,
and if so, the subquery to actually pluck the update value for the update.
In some other engines you might say (which would permit two tables to be
referenced, but only one be updated):
Update myref
Set upfu = dzhhq.upfu
From myref, dzhhq
Where myref.stkname = dzhhq.stkname
And myref.upfu <> dzhhq.upfu
And (select count(*)
from dzhhq as b
where b.stkname = myref.stkname) = 1;
This could run somewhat faster than the treble correlated subqueries in the
first or perhaps not significantly.
Both of the above are "clear expressions" of what you want to do. In the case
of SQLite if and only if the performance is insufficient despite having all the
requisite covering indexes,
the equivalent expressed using a trigger implementing update ... where current
of cursor against a view:
Create view updatemyref
As
Select myref.rowid, myref.upfu, dzhhq.upfu as newvalue
From myref, dzhhq
Where myref.stkname = dzhhq.stkname
And myref.upfu <> dzhhq.upfu
And (select count(*)
from dzhhq as b
where b.stkname = myref.stkname) = 1;
Create trigger updateupfu instead of update of upfu on updatemyref
Begin
Update myref
set upfu = new.upfu
Where rowid = old.rowid;
End;
Update udpatemyref
set upfu = newvalue;
The UPDATE statement gets all the rows from the view that represents the
updates that need to be made, then updates the "current of cursor" row values
trhough the trigger which actually performs the update against the underlying
table.
Of course, in all the above cases if dzhhq.stkname is constrained unique, the
check to ensure there is one and only one matching row in dzhhq can be omitted.
Similarly if you do not care whether or not there are extraneous updates made
you can remove the check to ensure that the value will actually be changed.
** if upfu in either table permits null values, you may have to make
accomodation for this fact as I did not consider this while writing the update
-- something has to be left as an exercise for the reader :) **
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -----Original Message-----
> From: [email protected] [mailto:sqlite-users-
> [email protected]] On Behalf Of YAN HONG YE
> Sent: Wednesday, 09 May, 2012 19:29
> To: [email protected]
> Subject: Re: [sqlite] sqlite-users Digest, Vol 53, Issue 9
>
> On Wed, 9 May 2012 02:24:58 +0100
> Simon Slavin <[email protected]> wrote:
>
> > alter table myref add stkcode varchar(30);
> > update myref set stkcode = dzhhq.stkcode;
> >
> > this sql command couldn't run in my sqlite.
>
> It does not know which row from the table dzhhq it is meant to use.
>
> Simon.
>
> I means:
> use another table column, to update the current table column: like this:
> alter table myref add upfu;
> update myref set upfu =dzhhq.upfu where exists (select * from dzhhq where
> myref.stkname=dzhhq.stkname);
>
> but it said no such column: dzhhq.upfu
> but dzhhq.upfu really exist.
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
