CODEWITHSUNDEEP
postgresqlsql-update
Joshua Rajandiran
Joshua Rajandiran

Reputation: 2928

Updating for each row in a table

I have this query here which returns an error because of too many rows returned:

UPDATE tmp_rsl2 SET comm_percent=( SELECT c2.comm_percent
FROM tmp_rsl2 t1 
INNER JOIN gn_salesperson g1 ON t1.sales_person=g1.sales_person
INNER JOIN comm_schema c1 ON g1.comm_schema=c1.comm_schema
INNER JOIN comm_schema_dt c2 ON c1.comm_schema_id=c2.comm_schema_id AND (t1.balance_amount::numeric <= (COALESCE(c2.value_amount,0)) );`

Basically for each row of the comm_percent column, I want to update all of them using the subquery SELECT statement. I imagine using a FOR loop or something but I'd like to hear ideas or to know a proper way to do this.

Upvotes: 1

Views: 2081

Answers (1)

Robins Tharakan
Robins Tharakan

Reputation: 2473

The error TOO_MANY_ROWS is about assigning a value to a variable, that can only take '1' (one) value, whereas the SELECT query is returning more than one.

Without a reference schema, its difficult to give an SQL that'd work (not to say that the issue lies with the Schema), but you need to ensure that the value assigned to comm_percent from the SELECT statement returns only 1 row. A very blind attempt at how it 'might' work in your case (given below), but again without knowing the schema its difficult to gauge whether it'd work.

UPDATE tmp_rsl2
SET comm_percent = c2.comm_percent
FROM gn_salesperson g1 ON 
  INNER JOIN comm_schema c1 ON g1.comm_schema = c1.comm_schema
  INNER JOIN comm_schema_dt c2 ON c1.comm_schema_id = c2.comm_schema_id
    AND (tmp_rsl2.balance_amount::NUMERIC <= (COALESCE(c2.value_amount, 0)))
WHERE tmp_rsl2.sales_person = g1.sales_person

UPDATE

As per below comments, have given an unrelated SQLFiddle example that should give an idea of how to perform an UPDATE of all rows of a table looking up corresponding values from another table.

Upvotes: 2

Related Questions