Is it possible to UPDATE a temporary table

[patrickbachmann]

Hi folks,

I have a temporary table that I've successfully inserted records into using INSERT.  Now I'm trying to perform an update like this;

UPDATE "#RECEIPTS" SET QTY = 1000 WHERE QTY = 1001

or

UPDATE #RECEIPTS SET QTY = 1000 WHERE QTY = 1001

Neither works.  I get error 'feature not supported: #RECEIPTS'

Is it not possible to perform UPDATES into a temporary table in HANA?  Also I have given _SYS_REPO update privilege for my schema with 'grantable'.  ;o)

Thanks!

-Patrick

[former_member182302 · Accepted solution]

Hi Partick,

It seems like we can do updates on "Local Temporary Table" but we cannot do on "Local Temporary Column Table".

Just tried on the example from the previous thread on "Temporary Table"

CREATE PROCEDURE LARSEXAMPLE ( )
LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER
  AS          
/********* Begin Procedure Script ************/
BEGIN
    create local temporary table #mytemp (id integer, name varchar(20));

    insert into #mytemp values (1, 'Lars');
    update #mytemp set name = 'breddemann' where id = 1;

    select * from #mytemp;
END;
/********* End Procedure Script ************/

call larsexample;

But when i tried with local temporary column table , i get this "Feature not supported" message.

I wonder why such a limitation on "Column" temporary table. Am sure there would be an logical reason for this behavior. ( which i could not get it )

Regards,

Krishna Tangudu

Regards,

Krishna Tangudu

[lbreddemann · Accepted solution]

Hey Krishna,

not sure what to think about being your guinea pig for your temp table experiments

Anyhow, my guess on the reason for the missing delete/update functions on the temp column tables is that this would require much more complex handling within the data structure.

As this is a column store table we can assume that the structure looks pretty much the same as for the persisted tables.

However, when all we need to support is adding new records or throwing them all away (think of update as DELETE and INSERT), then this is much simpler.

That's of course just my two cents.

- Lars

[patrickbachmann · Accepted solution]

Khrisna, that is indeed interesting.  I will now experiment using a row store and see what happens!  Thanks.

-Patrick

[former_member182302 · Accepted solution]

Hi Lars ,

I was just re-using the example which you posted in the below thread.

I agree with you and i saw the Delete + Inserts are performing better than "Updates" even on the column store table ( Persisted).. So this may be the reason they might have disabled "Update" operation for "Local Column Temporary Table".

I was actually trying to understand when to use Row Temporary Table and when to use Column Temporary Table. And what are the points to be considered while finalizing our approach.

So it seems like for Updates ( if we still want to use "Update") we need to use Row Temporary table and for other operations we can use "Local Column Temporary Table".. ( This is my understanding on what i saw till now )

Do correct me if my understanding is wrong.

Regards,

Krishna Tangudu

[justin_molenaur2]

Patrick, from what I know, temporary tables are just that - temporary. You can insert into them, but as soon as the stored procedure session ends, the contents are gone. An update would work only within the context of the stored procedure.

Is the insert you show above performed in SQL console or within a procedure?

If you performed a SELECT on this table in a SQL console window, do you get results?

I have used temp tables (table variables) in stored procedures without defining them beforehand, ie var_table = SELECT * FROM <table>, and then used in further processing. Not sure if what you are trying to do is something totally different.

Regards,

Justin

[lbreddemann]

Justin Molenaur wrote:

Patrick, from what I know, temporary tables are just that - temporary. You can insert into them, but as soon as the stored procedure session ends, the contents are gone. An update would work only within the context of the stored procedure.

That's nearly correct - the lifetime of temporary tables looks like this:

	global temporary table	local temporary table
structure	persisted - table structure will be available after the end of the creating transaction	not persisted - table structure is gone after the end of the transaction
data	data is private to the session that created it and will be deleted after the end of the transaction (COMMIT or ROLLBACK)	data is private to the session that created it and will be deleted after the end of the transaction (COMMIT or ROLLBACK)

All this and the list of supported operations on the different types of temp. tables can be found here CREATE TABLE - SQL Reference - SAP Library.

- Lars

Message was edited by: Lars Breddemann >>> Of course the data stays as long as the session exists ...