update&delete TEMP TABLE IN HANA

[byungseok_lee]

Hello Experts,,i'm a new to SAP HANA

Can i  update&delete temp table in hana ?

i can insert data into temp table in hana but i can not delete & update

------sample---------------------------------------------

DELETE FROM #FTISLAND WHERE "QTY" = '7'

UPDATE #FTISLAND
SET "QTY" = '11'

SAP DBTech JDBC: [7] (at 8): feature not supported: update statement for volatile table:
---------------------------------------------------------

Best regards and thanks to all of you that have helpfull answears, Thanks.

[Former Member · Accepted solution]

Hi Lee,

You can insert/update/delete in temp table, you should be use local temp table.

I try it, it is ok base on follow script, you can reference.

create local temporary table #"temp_table" (fyear VARCHAR(4),fperiod VARCHAR(2));

select * from #"temp_table"

insert into #"temp_table" values('2001','01');

update #"temp_table" set fyear='2003';

delete from #"temp_table".

Besides,please refer to below thread, it maybe can help you.

http://scn.sap.com/thread/3254364

Hope this can help you.

Regards,

Jerry

[byungseok_lee · Accepted solution]

It was very helpful to me.    thanks ~

[Former Member · Accepted solution]

Just be aware that temp tables, defined as

LOCAL TEMPORARY TABLE

or

GLOBAL TEMPORARY TABLE

are row-store tables. Column-store temp tables are not supported unfortunately.

Keep the above in mind when doing operations against temp tables - if you have large datasets, the data transfer from row to column storage can be quite expensive.

[Former Member · Accepted solution]

Hi

Not sure since which revision, but currently Hana supports column temporary tables. So make sure to use them when joining to other column based tables

Artem

[Former Member · Accepted solution]

We are working on VERSION: 1.00.68.384084 i.e. SP06, ("SYS"."M_DATABASE") and as per some threads issue of “updating Temp table with in an SP” has been resolved by SP05.

Still, we are getting following error message for temporary Table update.

SAP DBTech JDBC: [7] (at xxx): feature not supported: #TEMPDETAIL: line xxx col xx (at pos xxxx)

[Former Member]

We are working on VERSION: 1.00.68.384084 i.e. SP06, ("SYS"."M_DATABASE") and as per some threads issue of “updating Temp table with in an SP” has been resolved by SP05.

Still, we are getting following error message for temporary Table update.

SAP DBTech JDBC: [7] (at xxx): feature not supported: #TEMPDETAIL: line xxx col xx (at pos xxxx)

Our Code

-- Drop if exists in current session

SELECT COUNT (*) INTO v_COUNT FROM M_TEMPORARY_TABLES WHERE SCHEMA_NAME='SYSTEM'AND UPPER(TABLE_NAME)='#TEMPDETAIL';

      IF v_COUNT>=1 THEN

DROP TABLE #TEMPDETAIL;

      END IF;

--Creating

      CREATE LOCAL TEMPORARY TABLE #TEMPDETAIL(

ArticleOrSKUID integer,      

      StoreCode varchar(20),

      AVERAGE_WEEKLY_SALE decimal(18,2));

-- Update

Update #TEMPDETAIL SET AVERAGE_WEEKLY_SALE=50;

Need suggestion?