Shuhratjon Jumaev Shuhratjon Jumaev - 8 months ago 45
PHP Question

select with update in one query

I have a query like:

SELECT id, name, surname, fromId, toId, msg_text, readed FROM messages WHERE toId = 2;

So I want to
update all selected rows.readed = 1
. And Query must return all selected rows.

These action must do in one query if possibe.

Sorry for my english


Short answer: No, it is not possible in a single query.

A little less short answer: There is something known as a command-query separation which in short suggests that a command should do something and return nothing and a query should do nothing and return something. I recommend following this principle if you intend on building good software.

I wont get into why this is not possible because I myself am not that much of an SQL guru and I could only guess but I would suggest an easy solution to your problem.

When you get your results then you are most likely processing them in PHP. Assuming the results are sorted in ascending order - on the first iteration grab the minimum id and on the last one grab the maximum id, then run an update query:

UPDATE messages SET readed = 1 WHERE id >= <minimum id> AND id <= <maximum id>

On a side note - name and surname are probably not what you want to store in a messages table.