PostgreSQL: update field with select statement on the same table

I need to update a field in a table on a sample randomly selected. If I run this query it returns correctly the selected 57 rows:

SELECT * FROM table WHERE item1 = 'XX' AND item2 = 'JJJ' ORDER BY random() LIMIT (57)

开发者_JAVA百科

What I want to do now is to apply that SELCT statement to UPDATE the item1 with 'YY', but only for the 57 randomly selected rows. I try this:

UPDATE table
SET item1 = 'YY'
WHERE id_item = (SELECT id_item FROM table WHERE item1 = 'XX' AND item2 = 'JJJ' 
ORDER BY random() LIMIT (57))

but it returns the error: "more than one row returned by a subquery used as an expression".

How can I update a table for a random number of rows? Thanks in advance

---

Change the "=" operator to be IN instead.

UPDATE table
SET item1 = 'YY'
WHERE id_item IN (SELECT id_item 
                  FROM table WHERE item1 = 'XX' AND item2 = 'JJJ' 
                  ORDER BY random() LIMIT (57))

---

Chage = to "in".

You need to change it to this:

UPDATE table SET item1 = 'YY' WHERE id_item in (SELECT id_item FROM table WHERE item1 = 'XX' AND item2 = 'JJJ' ORDER BY random() LIMIT (57))

---

You may need to use IN operator instead of = for WHERE clause.

UPDATE table
SET item1 = 'YY'
WHERE id_item IN (SELECT id_item FROM table WHERE item1 = 'XX' AND item2 = 'JJJ' 
ORDER BY random() LIMIT (57))**