Kate Legere wrote:

I have a dtml-method that calls a bunch of sql inserts to update tables in a relational database. The last thing I need to do is retrieve the auto-increment ID from one table and use an update command to put it into a joining table. I'm using MySQL 3.23.52 and it won't let me write the call as :

UPDATE linksProjects SET linksProjects.categoryId = categories.categoryId (SELECT categories.categoryId FROM categories WHERE categories.catTitle = <dtml-sqlvar catTitle type=string>)

Apparently it won't let me reference two tables in a nested query at all.

So if I just make a call to a select statement:

SELECT categories.categoryId FROM categories WHERE categories.catTitle = <dtml-sqlvar catTitle type=string>

How do I pass the value retrieved back to a variable ... say 'ct' to use in The update statement?

Kate

Try this example ZSQL method (named insert_stuff): INSERT INTO some_table (stuff) VALUES (<dtml-sqlvar stuff type=string>) <dtml-var sql_delimiter> SELECT LAST_INSERT_ID() as last_insert_id And retrieve the last_insert_id from a python script: results = context.insert_stuff(stuff='more stuff') if results: return results[0].last_insert_id I don't do DTML but the concept will be similar. Jim