Updating a table within a select statement

Question

Is there any way to update a table within the select_expr part of a mysql select query. Here is an example of what I am trying to achieve:

SELECT id, name, (UPDATE tbl2 SET currname = tbl.name WHERE tbl2.id = tbl.id) FROM tbl;

This gives me an error in mysql, but I dont see why this shouldn't be possible as long as I am not changing tbl.

Edit: I will clarify why I cant use an ordinary construct for this.

Here is the more complex example of the problem which I am working on:

SELECT id, (SELECT @var = col1 FROM tbl2), @var := @var+1, 
(UPDATE tbl2 SET col1 = @var) FROM tbl WHERE ...

So I am basically in a situation where I am incrementing a variable during the select statement and want to reflect this change as I am selecting the rows as I am using the value of this variable during the execution. The example given here can probably be implemented with other means, but the real example, which I wont post here due to there being too much unnecessary code, needs this functionality.

Answer 1

What results do you want? The results of the select, or of the update.

If you want to update based on the results of a query you can do it like this:

update table1 set value1 = x.value1 from (select value1, id from table2 where value1 = something)  as x where id = x.id

Answer 2

If your goal is to update tbl2 every time you query tbl1, then the best way to do that is to create a stored procedure to do it and wrap it in a transaction, possibly changing isolation levels if atomicity is needed.

You can't nest updates in selects.