-index.md

SQL Snippets

Table of Contents

1. Truncate Table
2. Update Table Column
3. Display Duplicate Values
4. Select Second Max Value

1.md

Truncate Table

Truncate table with disabled foreign key checks.

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE table_name;

SET FOREIGN_KEY_CHECKS=1;

2.md

Update Table Column

Update a single table column and set new value.

UPDATE table_name SET column_name = NULL;

Update a single table column where the condition is met and set the new value.

UPDATE table_name SET column_name = NULL WHERE condition;

3.md

Display Duplicate Values

SELECT column_name from table_name GROUP BY column_name HAVING count(column_name) > 1;

Example

Input:
Users table:
+----+---------+
| id | email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@b.com |
| 3  | a@b.com |
+----+---------+

Output: 
+---------+
| email   |
+---------+
| a@b.com |
+---------+

Explanation: a@b.com is repeated two times.

Solution

SELECT email from users GROUP BY email HAVING count(email) > 1;

4.md

Select Second Max Value

Select second max value from the table. If there is no second highest value, the query should report null.

Example

Input: 
+----+--------+
| id | value  |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

Output: 
+---------------------+
| SecondHighestValue  |
+---------------------+
| 200                 |
+---------------------+

Input: 
+----+--------+
| id | value  |
+----+--------+
| 1  | 100    |
+----+--------+

Output: 
+---------------------+
| SecondHighestValue  |
+---------------------+
| null                |
+---------------------+

Solution

SELECT MAX(value) as SecondHighestValue FROM TableName WHERE value < (SELECT MAX(value) FROM TableName);