Do More in SQL, Less in Procedural Code

From WikiContent

(Difference between revisions)
Jump to: navigation, search
(New page: In the beginning, when Codd created SQL, the world of database systems was a formless wasteland, and darkness covered the abyss, while a mighty wind swept over the waters. Then Codd said,...)
Line 1: Line 1:
In the beginning, when Codd created SQL, the world of database systems was a formless wasteland, and darkness covered the abyss, while a mighty wind swept over the waters. Then Codd said, "Let there be SELECT, INSERT, UPDATE, and DELETE". And it was good...
In the beginning, when Codd created SQL, the world of database systems was a formless wasteland, and darkness covered the abyss, while a mighty wind swept over the waters. Then Codd said, "Let there be SELECT, INSERT, UPDATE, and DELETE". And it was good...
-
But if you're still writing code using only the original four DML statements, you're missing out on some very powerful, compact statements that can eliminate the need for heaps of procedural code.
+
But if you're still writing code using only the original four DML statements, you're missing out on some very powerful, compact statements that can eliminate the need for heaps of procedural code. For example, MySQL, SQL Server, and Oracle Database all include a single statement that will either insert data into a table if the primary key value doesn't exist, or update the existing row if the key does exist. Consider, for example, a message-based order processing system where individual messages may contain information about new orders or changes to existing orders. Every time a message is consumed, a decision needs to be made as to whether a row needs to be inserted or an existing row needs to be updated. You could write this logic yourself using a procedural language such as PL/SQL or Transact SQL, but why not let the server handle it for you?
 +
 
 +
For these types of operations, Oracle Database and SQL Server include the MERGE command, while MySQL uses an additional clause on the INSERT statement (INSERT ... ON DUPLICATE KEY UPDATE ...). Gone are the days where you need to attempt to insert a row, trap a "duplicate primary key value" error, and then launch an UPDATE statement.

Revision as of 02:52, 6 August 2009

In the beginning, when Codd created SQL, the world of database systems was a formless wasteland, and darkness covered the abyss, while a mighty wind swept over the waters. Then Codd said, "Let there be SELECT, INSERT, UPDATE, and DELETE". And it was good...

But if you're still writing code using only the original four DML statements, you're missing out on some very powerful, compact statements that can eliminate the need for heaps of procedural code. For example, MySQL, SQL Server, and Oracle Database all include a single statement that will either insert data into a table if the primary key value doesn't exist, or update the existing row if the key does exist. Consider, for example, a message-based order processing system where individual messages may contain information about new orders or changes to existing orders. Every time a message is consumed, a decision needs to be made as to whether a row needs to be inserted or an existing row needs to be updated. You could write this logic yourself using a procedural language such as PL/SQL or Transact SQL, but why not let the server handle it for you?

For these types of operations, Oracle Database and SQL Server include the MERGE command, while MySQL uses an additional clause on the INSERT statement (INSERT ... ON DUPLICATE KEY UPDATE ...). Gone are the days where you need to attempt to insert a row, trap a "duplicate primary key value" error, and then launch an UPDATE statement.

Personal tools