1

Let's say I have a subtable called result.

SELECT school, grade 
  FROM (SELECT school, grade 
          FROM simulated_records 
         LIMIT 10) as result

How would i update the columns of this subtable? I tried:

UPDATE result 
   SET grade = 'A'

...but I get an error.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
user1072706
  • 573
  • 2
  • 8
  • 20
  • 1
    A named sub-select is not a table. You would update the simulated_records table directly. – tawman Mar 22 '12 at 02:50
  • 2
    possible duplicate of [updating table rows in postgres using subquery](http://stackoverflow.com/questions/6256610/updating-table-rows-in-postgres-using-subquery) – mechanical_meat Mar 22 '12 at 02:51

1 Answers1

2

sub*selects* are temporary, they can't be updated. It sounds like you want a temporary table

CREATE TEMP TABLE temp_grades AS
SELECT school, grade FROM simulated_records;
UPDATE temp_grades SET grade = 'A';

EDIT: re your comment:

UPDATE simulated_records
FROM (SELECT id FROM simulated_records WHERE school='Yale' LIMIT 10) AS result
SET grade='A'
WHERE id = result.id
RETURNING *;

The above uses the UPDATE FROM table operation using a subselect

EDIT 2: for second comment:

Assuming you don't have a typo maybe you have an old version. There's an alternative way to do this with better support:

UPDATE simulated_records SET grade = 'A' WHERE id IN
  (SELECT id FROM simulated_records WHERE school = 'Yale' LIMIT 10);
SpliFF
  • 38,186
  • 16
  • 91
  • 120
  • In reality what I want to do is update the first n elements of the original table that meet a certain criteria, say school is 'Yale'. Any idea? – user1072706 Mar 22 '12 at 03:01
  • be clearer, do you want to permanently modify the *original* table or not? – SpliFF Mar 22 '12 at 03:12
  • I get a syntax error ERROR: syntax error at or near "FROM" LINE 2: FROM (SELECT id FROM simulated_records WHERE school='Yale' L... ^ ********** Error ********** ERROR: syntax error at or near "FROM" SQL state: 42601 Character: 26 – user1072706 Mar 22 '12 at 04:08