Delete contents of table column

[former_member302630]

Hi,

I have a database table with approx 63 million records.

I have to delete the contents of 2 specific columns prior to running a piece of code.

Is there a delete statement for deleting the contents of only certain columns while still retaining the columns in the structure of the table?

Wouldn't the update statement have to be run for every row , thus affecting performance if used to replace data with a blank in a certian column?

Regards,

Cleo

[horst_keller]

Hmm, the business case sounds a bit strange, deleting columns of a database table before "running a piece of code". And all the other code accessing the table?

[former_member302630]

Actually it is a Ztable used for simulation of prices in SAP BW. 2 columns of the Ztable which get filled post running code requires to be blank mandatorily before the code is run. So have to delete the contents of the these 2 columns. This table is active table of a Z-DSO. it is not used by any other code.

[former_member302630]

these columns are flags which would be 'X', but we have to delete all the 'X's and make them blank.

[Former Member]

I believe there is no another way to update each row in the table.

[FabioPagoti]

From my understanding cleo.james does not need to delete the columns - the requirement is to clear some columns of some particular rows.

Why not just using an UPDATE with a WHERE clause?

[former_member302630]

does below statement go row by row or in one swoop for the whole database table?

update DBTABLE1 set col1 = 0, col2 = ' '.

[horst_keller]

All,

https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abapupdate_source....

[matt]

"requires to be blank mandatorily before the code is run."

What requires them to be blank? Why can the field values simply not be overwritten? Seems odd.

[PraveenChitturi]

Yes. There is no way without UPDATE SET statement.

[former_member302630]

but how does the UPDATE SET statement work? the SET column feature goes row by row for changing 1 column or just sets all the rows in the whole column at once?

[PraveenChitturi]

Hello James,

I am not sure why you are more concern whether it will update row by row (or) all the rows at once. This will be depend the DB that we are using.

The general syntax will be as below.

UPDATE ZMYTABLE SET DESC_FIELD = 'My test description in english'
            WHERE spras = 'EN' .

Commit work. 

[Sandra_Rossi]

ouch, direct update of a standard table? Can't you just take a Z table name for your example...

[matt]

Edited it.

@Praveen - you don't really update standard SAP tables like MAKT directly do you?

[PraveenChitturi]

Hi , I just tried to explain the keyword UPDATE SET . I don't do this in our productive environments 😞

Anyhow I will use Z tables for examples. 🙂

Thanks ,

Praveen.

[Sandra_Rossi]

In German?

[matt]

Here's the English.

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abapupdate_source.htm

[Sandra_Rossi]

An Open SQL statement is at the end operated by your database system, so go to the forums/documentation of the software vendor (Oracle, SAP HANA, Microsoft Server SQL, IBM DB/2, etc.)

But of course UPDATE ... SET ... is a well known SQL statement, which does a mass update, not one by one. In fact, technically, it will always be one by one, but it's not at the ABAP side of course, and it's always more performing than multiple updates.

Note that you may also define in the DDIC an update database view so to select only the primary key and columns you want to update, and do the UPDATE ... FROM TABLE update_database_view.

[matt]

I imagine if the table in question is a column store on a HANA database, it will be very fast.

[matt]

Have you tried it with UPDATE SET and checked if there is a performance problem?

[matt]

Create a new DSO without the two fields. Create a transformation and DTP from DSO1 to DSO2 - direct map, push down to HANA if you're at the right level and are using HANA. Create another transformation and DTP from DSO2 to DSO1.

Now, in your process chain, run the first DTP, drop DSO1 using CL_SQL_STATEMENT for native SQL, run the second DTP.

[pokrakam]

Why do I have this image of hammers and walnuts in my head? 🙂

[matt]

I couldn't possibly imagine. ;-D