using functions in an update query

[Russell]

Hi,

I'm trying to update some text fields in a database. In SQL I would
do something like this...

UPDATE products SET product_code=substr(product_code,2);

or even...

UPDATE products SET product_name = replace(product_name, 'Tiger',
'Woods');

Does anyone have an example of how to do this with the DAL?

Thanks
Russell

[selecta]

for p in db(db.products.id>0).select():
p.update(product_name = 'Tiger Woods')

or

[p.update(product_name = 'Tiger Woods') for p in
db(db.products.id>0).select()]

[mr.freeze]

It will be faster if the update is performed on the set (without
selecting each one):

db(db.products.id>0).update(product_code=db.products.product_code[:2])

You can do _update to see what SQL will be generated.

[Russell]

Thanks mr.freeze, that's got it. And so obvious too.

>>>db(db.products.id>0)._update(product_code=db.products.product_code[:2])
"UPDATE products SET product_code=SUBSTR(product_code,1,(2 - 0))"

I guess my question was mainly about the SQL scalar functions
supported by web2py, and the syntax for using them.

For the record, web2py DAL supports: upper(), lower(), len(), and
slicing. But not strip() or replace().