Menu
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.
809
The UPDATE from SELECT query structure is the main technique for performing these updates. An UPDATE query is used to change an existing row or rows in the database. UPDATE queries can change all tables' rows, or we can limit the update statement affects for certain rows with the help of the WHERE clause.
Open the database that contains the records you want to update. On the Create tab, in the Queries group, click Query Design. Click the Tables tab. Select the table or tables that contain the records that you want to update, click Add, and then click Close.
You can't. There's no convention in a SQL UPDATE statement for returning data.
Now what happens if you want to update rows in one table based on the condition of another table? This question leads to a few different ways you could do this. UPDATE table SET col = ( SELECT other_col FROM other_table WHERE other_table. table_id = table.id );
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.
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
