Core SAP HANA SQL script concepts- SQL UPDATE,DELETE,DROP
Welcome to the seventh installment of this SAP HANA SQL Scripts core concepts section where we learn how to manipulate data using SQL UPDATE, delete certain data using SQL DELETE and obliterate the table from existence using SQL DROP statement.
Till now, we have learnt the INSERT which inserts new data into a table and also SELECT which picks up data from the table. Now we learn the next database statement which is SQL UPDATE. This is used rarely but is still important. It is used to change existing values in a SAP HANA table.
SQL UPDATE – Time for changes…
Let’s take an example. We know from our examples that the employee experience for employee 1001 is NULL. Let’s change it to a value of 10.
Template is:
UPDATE <table_name>
SET <field_name> = <value/ field/ formula>
WHERE <condition>
The SET keyword specifies what goes into the result cell(s). This can be a constant value or can also take values from other fields of the table. Otherwise, a formula can also be used to calculate the result to be passed on.
The WHERE condition specifies the data sets on which this resultant value needs to be applied to.
The drafted code looks as below. The execution is confirmed by the log at the bottom of the screen which says Rows affected: 1.
To check if the SQL UPDATE operation worked, let’s do a SELECT on the EMP_EXP table for only employee ID 1001.
As seen from the execution result, the years of experience is now updated as 10 now. It was NULL (marked as ?) earlier. Thus our SQL UPDATE works as expected.
DELETE STATEMENT HANA
Now that we are clear on SQL UPDATE, it’s time to let the inner demons out and be a little destructive. In really rare circumstances, we require deletion of certain rows of data from a table. This is done via a DELETE statement.
Template is:
DELETE FROM <table_name>
WHERE <optional_condition>
The condition you specify decides which rows will be updated. If you specify no condition, all of the rows of data would be deleted. Only the table structure/metadata would remain with no data.
Be really careful with this statement. Double check with your project leads before you decide to do some deletions.
Let’s take an example to clear the data for employee ID 1001. We draft the DELETE statement as below. The log shows that 1 row was affected which is a positive confirmation.
Now let’s try to SELECT the one row for employee 1001
On execution we see that no such record exists anymore and hence our DELETE operation was successful.
Now, let’s get more destructive and run the delete without any conditions. As you see the log tells us that the other 49 rows have also been now cleared off.
COUNT function
Instead of doing a SELECT to check if our DELETE worked, let’s try to count the number of rows of data that are left in this table.
This is done using the COUNT function. COUNT() counts the number of rows of data that exists for a particular field. Also, using COUNT(*) would count the total number of rows existing in a table. To check the count of rows existing in this table after everything has been deleted from it, we do a COUNT(*) on EMP_EXP table as shown below.
The output as 0 confirms that our DELETION is successful.
Let’s take another example where we want to do a quick analysis of how many unique values for GENDER exist in EMP_MASTER table. So we run a COUNT(GENDER) on our EMP_MASTER table as shown below.
The result is 50. Which is because we told the system to pick up the number of times a gender value occurred in the table but did not tell it to pick up only unique occurrences.
To pick up counts of unique gender values, we add another keyword – DISTINCT in front of the field name GENDER and run this again.
This time we get the correct result that there are two distinct gender values that exist in the table(which we already know is ‘Male’ and ‘Female’)
DROP statement
The final and most destructive statement in SAP HANA is DROP. It deletes the table from existence along with its structure/metadata and data.
Template for this is:
DROP TABLE <table_name>
As simple as this statement is, this should be used with the utmost caution. Otherwise, this is one of those things that can cost you your job if you go wrong with it in a real time project. Always be 110% sure that you wish to DROP something before you actually proceed with it.
The below screenshot shows the statement being executed and the EMP_EXP table being dropped.
I hope this was easy to understand and follow. The next part will enlighten you on the SQL CASE statement which you can use to fill different values to the same field based on unique conditions. Also we learn the concept of SELECT inside SELECT.. a nested SELECT. Be sure to check it out.
Make sure to share this content on social media using the share buttons below to show your support to this website and to keep it alive.
<<Previous Tutorial Next Tutorial>>