CODEWITHSUNDEEP
sqlpostgresqlselect
James Dunn
James Dunn

Reputation: 59

Postgres: update column by id referenced in another table

I have 2 tables and I am attempting to change the value of a particular column based on extracting the row id from another table whose column matches a particular value.

Table 1 (cc_show) contains the column (background_color) and I want to change this value to a fixed value (ff0000) if Table 2 (cc_show_instances) column 'record' OR column 'rebroadcast' is equal to 1.

Furthermore, for all columns in Table 2 (cc_show_instances) whose 'record' OR 'rebroadcast' columns are not equal to 1, I would like to change the corresponding id in Table 1 to 0000ff.

I can search Table 2 (cc_show_instances) for all the positive results with the following command:

SELECT DISTINCT show_id 
FROM cc_show_instances 
WHERE rebroadcast = 1 
OR record = 1

I tried the putting this as a SELECT contained in the WHERE option as follows:

UPDATE cc_show 
   SET background_color = 'ff0000' 
WHERE id = (SELECT DISTINCT show_id 
            FROM cc_show_instances 
            WHERE rebroadcast = 1 OR record = 1);

but this returns the error:

ERROR: more than one row returned by a subquery used as an expression

Which I assume is because it is not iterating through the results. How can I achieve the desired results?

Upvotes: 0

Views: 2596

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

You have been told about IN in the comments.
Better yet, join to the second table in a FROM clause. That's a bit shorter and IN does not scale as well for big sets (at least in my tests in Postgres 8.4 - 9.2).

UPDATE cc_show s
SET    background_color = 'ff0000'
FROM   cc_show_instances si
WHERE  1 IN (si.rebroadcast, si.record)  -- IN is good for small sets
AND    s.id = si.show_id;

Upvotes: 1

Related Questions