Invalid operation: subquery must return only one column — Amazon Redshift

Question

i have pasted an image with wat o/p i need hope tht helps

I am trying to calculate the percentage of individual rows with total of one particular column.

I am able to achieve it in the SELECT statement with alias name but I have further calculation with the help of column per where I derive other columns, so I need this to be in an UPDATE query. I tried the below query but am getting the error:

Invalid operation: subquery must return only one column

I don't have any unique id. I cannot use IN statements in this.

Where am I going wrong?

UPDATE a
SET per = (SELECT DISTINCT
             code,
             p,
             pd,
             ratio_to_report(SUM(amnt)) OVER (PARTITION BY p) AS per
           FROM a 
           GROUP BY code,p,pd
           )
WHERE per IS null;

. . I removed the inconsistent database tags. Please tag only with the database you are using. Sample data and desired results would really help. — Gordon Linoff, Jun 28 '19 at 14:04
You should not store values which you count from other values. Remove column PER from table and create view instead. Otherwise you are asking for troubles like this. — Ponder Stibbons, Jun 28 '19 at 14:06
You need to provide some sample data and expected output. A [UPDATE with a JOIN](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) is most likely what you are looking for, but with your minimal code snippet it is impossible to fix your issue. But, in regards to your error, anytime you have `something = (select...)` the `(select...)` portion must only return a single row and column. You have selected multiple columns, which will also return multiple rows. How would all that data fit in a single row & column? — S3S, Jun 28 '19 at 14:07
yes i thnk i have to go with join. will be great help if i get some suggestion on tht — Rao, Jul 01 '19 at 06:32
i am able to achieve the percentage values in select statement but the per column stands as temporary and i need to use per column for other calculations further so im facing the difficulty — Rao, Jul 01 '19 at 06:34

score 0 · Answer 1 · answered Jun 29 '19 at 00:34

0

You're trying to update 1 column with 4 values. Imagine it without the subquery:

UPDATE a
SET per = code, p, pd, per
WHERE per IS null;

Your subquery must return a single column and row.

answered Jun 29 '19 at 00:34

TommyGunn32

924
1
8
22

i tried updating it with single column but still unsuccessfull – Rao Jul 01 '19 at 06:31

Joe Harris · Answer 2 · 2019-07-01T15:00:37.157

Your subquery returns multiple values and multiple rows. You need to tell Redshift exactly how to apply all of the returned values and rows to the table being UPDATE'ed.

For example:

UPDATE tbl_a
SET tbl_a.per = code_sum.per
FROM tbl_a
JOIN (SELECT code
           , SUM(amnt) AS per
      FROM tbl_a
      GROUP BY code
     ) code_sum
   ON tbl_a.code = code_sum.code
WHERE per IS NULL
;

Side note: For best query speed do not add DISTINCT to your queries unless you know you need distinct results. You are asking Redshift to perform an extra action that is not necessary as you already had a GROUP BY on the same columns. Often Redshift is able to skip the extra DISTINCT step but not always.

Invalid operation: subquery must return only one column — Amazon Redshift

2 Answers2