Populating a column with the result of a query from the same table

[Former Member]

Hello good people,

I want to populate a column("APR_REV") in my table by using an SQL query on the same table. The query is:

select "REVENUE" FROM "MySCHEMA".MYTABLE"

WHERE "Month" = 'APRIL' group by "ID", "REVENUE" ;

Note that Month stores strings

I tried  many different queries with the below one showing most promise as it showed rows effected in the result but nothing is printed in the column.

insert into "D063375"."MySCHEMA".MYTABLE"("APR_REV")

select "REVENUE" FROM "MySCHEMA".MYTABLE"

WHERE "Month" = 'APRIL' group by "ID", "REVENUE" ;

My question is, is it possible to do that? if yes any guidance on the how would be much appreciated as I'm a bit of a novice with HANA and SQL.

[SergioG_TX · Accepted solution]

when you are doing an insert, you need to specify all the columns in the table you are inserting to. assuming that your table only has that one column, then yes it will work. however, if you have X columns in the table, in order for the insert statement to work you will need to provide the columns and the values for the other columns you are trying to populate try (for table w one column) insert into "D063375"."MySCHEMA".MYTABLE"("APR_REV") select "REVENUE" as "ARP_DEV" FROM "MySCHEMA".MYTABLE" WHERE "Month" = 'APRIL' group by "ID", "REVENUE" ; or for more columns try: insert into "SCHEMA"."table"("colName","ColName2",...,"ColNAmeN" select 'val' as "ColName", val2 as "ColName2,...,valN as "ColNAmeN" from table -- etc now if you have a table which is already populated, then you need to do an update statement instead of an insert statement update table set YourColumn = aValue from table -- where col = val -- add where clause if you need to affect only certain records hope this helps

[Former Member · Accepted solution]

Thank you Sergio, my table is already populated with a million records. the column that I'm trying to populate is new and I want to populate it with a query on the revenue column in the same table so that I only get revenue for april in "APR_REV"

I tried the update query (without using group by as that wasnt working) but it still shows the same number of rows effected. but when I display content the column again hasnt been populated.

[SergioG_TX · Accepted solution]

do you have proper permissions/access to update the table? this should not be that difficult - an update is a fundamental operation... also make sure there are no triggers involved setting back the value

[Former Member · Accepted solution]

I do have all the necessary permissions and there are no triggers assosicated with the table which would prompt such behaviour

[SergioG_TX]

try this: do a select statement of what you need to update.. that way you know X number of rows will be updated, include an extra column that reflects the new value to update on the column to be updated if that is correct, save a screenshot.. write your update statement - post it so I can verify it.