This comes up once in a while you can do
>>> from gluon dal import Expression
>>> db=DAL()
>>> db.define_table('a',Field('name'))
>>> db.define_table('b',Field('name'),Field('a',db.a))
>>> print
db(db.a)._update(name=Expression(db,db(db.b.a==db.a.id)._select(db.b.id)))
UPDATE a SET name=(SELECT b.id FROM a, b WHERE (b.a = a.id)) WHERE (a.id >
0);
I made a change in trunk to make it easier:
>>> print db(db.a)._update(name=db(db.b.a==db.a.id).nested_select(db.b.id))
UPDATE a SET name=(SELECT b.id FROM a, b WHERE (b.a = a.id)) WHERE (a.id >
0);
On Monday, 21 May 2012 01:16:04 UTC-5, Simon Ashley wrote:
>
> To answer my own post, syntax should have been:
>
> db.executesql("UPDATE Plant SET SiteID=(SELECT Site.id FROM Site WHERE
> Site.SiteId = Plant.SiteIdLink);")
>
>
> Sorry about that ...
>
> On Monday, May 21, 2012 2:50:33 PM UTC+10, Simon Ashley wrote:
>>
>> trying the following expression but get an update according to the first
>> record rather than being conditional on specific records processed:
>>
>> db.executesql("UPDATE Plant SET SiteID=(SELECT Site.id FROM Plant, Site
>> WHERE Site.SiteId = Plant.SiteIdLink);")
>>
>>
>> ie. all SiteID's get the value of the first Site.SiteId =
>> Plant.SiteIdLink encountered.
>>
>> Just can't see the solution at present.
>>
>
