Keeping Data Up to Date with the HANA Academy
Keeping Data Up to Date with the HANA Academy
Beginnings
Who remembers their first Greenfield database project? I do. My three children were small and I used to bike a marathon to work and back over a mountain through the fog each day.
The Basic SQL Series
During my first professional foray into databases I used many skills that are extremely well explained by the SAP HANA Academy in the Basic SQL series. This series includes 20 videos whose aim is to introduce you to the Structured Query Language at a very basic level. However, because I had this very lesson in person before it was available to you all I am going to focus on one of the sections I found very useful – Update Queries.
Where did I apply the learning which inspired the Basic SQL Series?
Working with others I built bespoke systems in MS Access and SQL to act an electronic mark book for teachers which could be shared with students in real-time. Query design was extremely important for performance hence the focus of this blog post.
Basic SQL 11 Update
This tutorial looks at updating an existing record using the SQL statement when using the SAP HANA Studio. Its starts logically enough from SAP HANA Studio using the table structure below.
In this simple example car registrations are changed using an UPDATE statement. The explanation I know by heart as it was how Bob explained it to me many years ago. Using the SQL editor you type in the statement below.
update “XTRA”,”MYTESTTABLE”
set CARREGISTRATION = 54367
where CARID = 21
You then get confirmation below that the statement was indeed executed and a row changed.
When you go back to the table and refresh you can see that the car registration has indeed been updated.
The tutorial then explains that you can do this multiple times and prompts you to use the semi-colon to separate statements before you copy the UPDATE statement. The tutorial then shows how to change the registration on rows 3 and 4 to be the same using a greater than symbol “>”. You can see below that this has changed 2 rows
Returning back to the test table and refreshing it you can see that rows 3 and 4 have changed to 7786.
Why did I focus on Update Queries?
Believe it or not when I first started making database tracking systems I did not know that queries could be used to update, delete or append data. I remember going to Bob with the following issue. Teachers wanted a method of forcing the marks that they entered into the students individual mark sheet. Even though there was only a slight delay over the network, students wanted the mark on the teacher’s board to be updated on the local version of the database which held only their marks. From what looked like a spreadsheet on the teacher’s board to a report that looked exactly like the pro-formas used across the country by the exam board on their desktops it never ceased to amaze visitors to the school. The update queries could have been set to run automatically but teachers loved the drama of pressing the button which updated all the marks on students’ desktops simultaneously.
Why I recommend the Basic SQL Series
What is really excellent about this set of tutorials is that they are self contained and that their place in the series is clearly indicated at the start of each tutorial. The examples for illustration purposes are relevant and as my preamble shows, I and many others have benefitted from the level of instruction they include. They are bite sized and to the point. I have found them incredibly useful and know that anyone wanting to learn SQL using the HANA platform will as well. So please share the resources on Basic SQL put together by the SAP HANA Academy. They provide an excellent grounding in SQL and will help people starting out in SQL quickly gain confidence in their skills.