update rows in a single table based on join
I have a Query that produces rows that I wish to update, and the query looks like this:
SELECT item.item_id
FROM items
JOIN users2items
ON users2items.item_id = items.item_id
WHERE users2items.user_id = 10;
Now I need to update a column for each record that matches that query in the item table; currently I'm taking the result set and then building and executin开发者_开发知识库g queries for each result, but I figure there is probably a way to do this directly in SQL. The individual statements I'm executing are:
UPDATE items SET is_usable = 1 WHERE item_id = $current_id
So it works as is, but I'm trying to learn if there's a pure-SQL way to do it
UPDATE items
JOIN users2items
ON users2items.item_id = items.item_id
SET is_usable = 1
WHERE users2items.user_id = 10
精彩评论