Principles of Database Systems (H) Lecture 8 Notes

Data Modification

UPDATE

UPDATE is the command to changes column values, the change is applied to all rows selected by the WHERE clause:

UPDATE table_name
SET column_name = new_value
	other_col = other_val
	...
WHERE ...

Example: suppose we want to change all surnames that contains the nobiliary particle “von” from prefix to suffix, s.t. they can be sorted correctly:

UPDATE people
SET surname = substr(surname, 4) || ' (von)'
WHERE surname LIKE 'von %'

Tip

Always prefer batching modifications instead of doing updates in loops.

UPDATEs can be subtle when we want to update a table with data coming from another table. SQLite:

UPDATE movies_2
SET duration = (SELECT duration
				FROM us_movie_info i
				WHERE i.title = movies_2.title
				  AND i.year_released = movies_2.year_released),
	color = (SELECT CASE color
					  WHEN 'C' THEN 'Y'
					  WHEN 'B' THEN 'N'
					END color
			 FROM us_movie_info i
			 WHERE i=title = movies_2.title
			   AND i.year_released = movies_2.year_released)
WHERE country = 'us'
  AND EXISTS (SELECT NULL
			  FROM us_movie_info i2
			  WHERE i2.title = movies_2.title
			    AND i2.year_released = movies_2.year_released)

Oracle, DB2:

UPDATE movies_2
SET (duration, color) =
	(SELECT duration,
			CASE color
			  WHEN 'C' THEN 'Y'
			  WHEN 'B' THEN 'N'
			END color
	 FROM us_movie_info i
	 WHERE i.title = movies_2.title
	   AMD i.year_released = movies_2.year_released)
WHERE country = 'us'
  AND EXISTS (SELECT NULL
			  FROM us_movie_info i2
			  WHERE i2.title = movies_2.title
			    AND i2.year_released = movies_2.year_released)

SQLServer, PostgreSQL:

UPDATE movies_2
SET duration = i.duration
	color = CASE i.color
			  WHEN 'C' THEN 'Y'
			  WHEN 'B' THEN 'N'
			END
FROM us_movie_info i
WHERE i.title = movies_2.title
  AND i.year_released = movies_2.year_released
  AND movies_2.country = 'us'

MySQL:

UPDATE movies_2 m
	   INNER JOIN us_movie_info i
		  ON i.title = m.title
		 AND i.year_released = m.year_released
SET m.duration = i.duration,
	m.color = CASE i.color
				WHEN 'C' THEN 'Y'
				WHEN 'B' THEN 'N'
			  END
WHERE m.country = 'us'

Warning

If we forget conditions in updates like above ones s.t. a update would affect several rows identical in primary keys, the approaches using a subquery would generate an error, but the approaches using JOIN won’t fail and update randomly.

Warning

It’s usually forbidden to update a key, you can only delete the row and insert another.

An interesting operation would be to update a film we know, and insert it if we don’t. This is when MERGE comes into play (supported by SQLServer, Oracle, and DB2):

MERGE INTO movies_2 m
USING (SELECT 'us' AS country,
			  title
			  year_released
			  duration
			  CASE color
				WHEN 'C' THEN 'Y'
				WHEN 'B' THEN 'N'
			  END AS color
	   FROM us_movie_info) i
	ON (i.country = m.country
   AND i.title = m.title
   AND i.year_released = m.year_released)
WHEN MATCHED THEN
	UPDATE
	SET m.duration = i.duration
		m.color = i.color
WHEN NOT MATCHED THEN
	INSERT(title, year_released, country, duration, color)
	VALUES(i.title, i.year_released, i.country, i.duration, i.color)

MySQL can catch an insert that duplication failures, and turn on the fly the insert into an update:

INSERT INTO movies_2(title, year_released,
					 country, duration, color)
SELECT title, year_released, country, duration, color
FROM (SELECT title,
			 year_released,
			 'us' AS country,
			 duration,
			 CASE color
			   WHEN 'C' THEN 'Y'
			   WHEN 'B' THEN 'N'
			 END color
	  FROM us_movie_info) i
ON DUPLICATE KEY UPDATE
	movies_2.duration = i.duration,
	movies_2.color = i.color

SQLite allows something similar with a simpler syntax. Beware since it deletes a row and creates a new one, the foreign key constraints might be violated:

INSERT OR REPLACE INTO movies_2(title, year_released,
								country, duration, color)
SELECT title, year_released, country, duration, color
FROM (SELECT title,
			 year_released,
			 'us' AS country,
			 duration,
			 CASE color
			   WHEN 'C' THEN 'Y'
			   WHEN 'B' THEN 'N'
			 END color
	  FROM us_movie_info) i

Or we can simply use an UPDATE + INSERT to achieve the same functionality.

DELETE

Deletions are often logical, however, to keep volumes under control, it’s frequent to copy old rows to a history table, then delete them from the “active” table.

Caution

When doing DELETEs, always write a WHERE clause.

As DELETE saves data for rollback before removing it, it can be slow. There is a TRUNCATE (without a WHERE clause) that cannot be rolled back and is far more efficient. It’s better not to use it.

Functions

Suppose to aid sorting, we store the names like “Erich von Stroheim” as “Erich Stroheim (von)” in database, now we want a function to return the original name from the stored version:

CREATE FUNCTION full_name(p_fname varchar, p_sname varchar)
RETURNS varchar
AS $$
BEGIN
	RETURN CASE
		     WHEN p_fname IS NULL THEN ''
		     ELSE p_fname || ' '
		   END |
			 CASE POSITION('(' IN p_sname)
			   WHEN 0 THEN p_sname
			   ELSE TRIM(')' FROM substr(surname,
										 POSITION('(' IN surname) + 1))
					|| ' '
					|| TRIM(substr(surname, 1,
								   POSITION('(' IN surname) - 1))
END full_name
$$ LANGUAGE plpgsql;

Once the function is created, it will be store in the database.

Procedural extensions to SQL:

• SQLServer: T-SQL
• MySQL: (no name)
• Oracle: PL/SQL
• PostgreSQL: PL/PGSQL
• IBM DB2: SQL PL
• SQLite: no extension

(You can use C or any programming languages with SQLIte.)

To retrieve data from the database into variables, we need to use SELECT...INTO... if the query returns a single row, or we can use cursors, which are kind of iterators that traverse the rows of a table.

SELECT col1, col2, ...
INTO local_var1, local_var2, ...
FROM ...

Caution

Always prefer a relational operation than a procedural approach. Since the query optimizer can well understand and optimize the former, but optimizing the latter is a least a magnitude harder.