Python Tuples

Python Tutorial › Data Structures › Tuples

Python Tuples

BeginnerLesson 20 of 479 min read11 runnable examples

A tuple is an ordered collection that cannot be changed after it is created. Use tuples for fixed groups of values, such as coordinates, RGB colours or a database row, and to return several values from a function.

In this lesson
  • Create tuples, including one-item tuples
  • Access items by index and slice
  • Unpack a tuple into variables
  • Know when a tuple beats a list

Create a tuple

Write items separated by commas, usually inside round brackets.

Python
point = (12.5, 77.3)rgb = (255, 128, 0)mixed = ("Asha", 29, True)empty = () print(point, type(point).__name__)print(len(mixed), len(empty))
Output
(12.5, 77.3) tuple
3 0

The commas make the tuple, not the brackets. You can even leave the brackets out:

Python
version = 3, 12, 1print(version)
Output
(3, 12, 1)

The one-item trap

A single value in brackets is just that value. Add a trailing comma to make a one-item tuple:

Python
not_a_tuple = (5)one_item = (5,)print(type(not_a_tuple).__name__, type(one_item).__name__)
Output
int tuple

Access items

Indexing and slicing work exactly as with lists and strings, and a slice of a tuple is a new tuple.

Python
days = ("Mon", "Tue", "Wed", "Thu", "Fri")print(days[0], days[-1])print(days[1:4])print("Wed" in days) for i, day in enumerate(days, 1):    print(i, day, end="  ")
Output
Mon Fri
('Tue', 'Wed', 'Thu')
True
1 Mon  2 Tue  3 Wed  4 Thu  5 Fri  

Tuples are immutable

You cannot add, remove or replace items. Trying to raises a TypeError:

Python
rgb = (255, 128, 0)rgb[0] = 200
Output
Traceback (most recent call last):
  File "main.py", line 2, in <module>
    rgb[0] = 200
    ~~~^^^
TypeError: 'tuple' object does not support item assignment

To “change” a tuple, build a new one:

Python
rgb = (255, 128, 0)rgb = (200,) + rgb[1:]print(rgb)print(rgb * 2)
Output
(200, 128, 0)
(200, 128, 0, 200, 128, 0)
Note

A tuple can contain a mutable object such as a list, and that list can still change. The tuple itself still holds the same objects, so it is called “shallowly” immutable.

Unpacking

Assign a tuple’s items to several names in one step. This is used everywhere in Python.

Python
name, age, city = ("Ravi", 31, "Pune")print(name, age, city) first, *middle, last = (1, 2, 3, 4, 5)print(first, middle, last) def min_max(values):    return min(values), max(values)      # returns a tuple low, high = min_max([7, 2, 9, 4])print(low, high)
Output
Ravi 31 Pune
1 [2, 3, 4] 5
2 9

Tuple methods

Because tuples cannot change, they have only two methods:

Python
marks = (90, 75, 90, 60, 90)print(marks.count(90))print(marks.index(75))print(sorted(marks), min(marks), max(marks), sum(marks))
Output
3
1
[60, 75, 90, 90, 90] 60 90 405

Tuples as dictionary keys

Tuples can be dictionary keys and set members because they are immutable (and hashable). Lists cannot.

Python
distances = {("Pune", "Mumbai"): 150, ("Pune", "Nashik"): 210}print(distances[("Pune", "Mumbai")])
Output
150
Python
bad = {["Pune", "Mumbai"]: 150}
Output
Traceback (most recent call last):
  File "main.py", line 1, in <module>
    bad = {["Pune", "Mumbai"]: 150}
          ^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: cannot use 'list' as a dict key (unhashable type: 'list')

Named tuples

collections.namedtuple gives each position a name, so code reads p.x instead of p[0]:

Python
from collections import namedtuple Point = namedtuple("Point", ["x", "y"])p = Point(3, 4)print(p)print(p.x, p.y, p[0])
Output
Point(x=3, y=4)
3 4 3

Check your understanding

Predict the output

t = (1, [2, 3], 4)t[1].append(99)print(t, len(t))

Quick quiz

1. Which of these is a tuple with one item?

2. How many methods does a tuple have?

3. Can a tuple be a dictionary key?

Exercise: student records

Each record is a (name, marks) tuple. Print the topper and the class average, using unpacking in the loop.

Input
[("Asha", 88), ("Ravi", 74), ("Meena", 93)]
Expected output
Topper: Meena 93
Average: 85.0
Hint 1

max(records, key=lambda r: r[1]) finds the record with the highest marks.

Program Practice

Python tuples FAQ

What is the difference between a list and a tuple in Python?

A list is mutable and written with [ ]; a tuple is immutable and written with ( ). See List vs Tuple for a full comparison.

How do I create a tuple with one element?

Add a trailing comma: t = (5,) or just t = 5,.

Can I add an element to a tuple?

Not in place. Create a new tuple: t = t + (new_item,).

How do I convert a list to a tuple?

Use tuple(my_list). Use list(my_tuple) to go the other way.

Python String Methods

  1. len(): Returns the length of a string. Here’s an example:
# Using len() to get the length of a string
my_string = "Python Programming"

# Get the length of the string
length = len(my_string)

print("The length of the string is:", length) # Output: 18
  1. lower(): Converts all characters in a string to lowercase. Here’s an example:
# Using lower() to convert string to lowercase
my_string = "Python Programming"

# Convert the string to lowercase
lowercase_string = my_string.lower()

print("Original string:", my_string) # Output: Python Programming
print("Lowercase string:", lowercase_string) # Output: python programming
  1. upper(): Converts all characters in a string to uppercase. Here’s an example:
# Using upper() to convert string to uppercase
my_string = "Python Programming"

# Convert the string to uppercase
uppercase_string = my_string.upper()

print("Original string:", my_string) # Output: Python Programming
print("Uppercase string:", uppercase_string) # Output: PYTHON PROGRAMMING
  1. title(): Capitalizes the first character of each word in a string. Here’s an example:
# Using title() to capitalize the first character of each word
my_string = "python programming is fun"

# Convert the string to title case
title_string = my_string.title()

print("Original string:", my_string) # Output: python programming is fun
print("Title case string:", title_string) # Output: Python Programming Is Fun
  1. capitalize(): Capitalizes the first character of a string. Here’s an example:
# Using capitalize() to capitalize the first character of a string
my_string = "python programming"

# Capitalize the first character
capitalized_string = my_string.capitalize()

print("Original string:", my_string) # Output: python programming
print("Capitalized string:", capitalized_string) # Output: Python programming
  1. swapcase(): Swaps the case of all characters in a string. Here’s an example:
# Using swapcase() to swap the case of all characters in a string
my_string = "Python Programming"

# Swap the case of all characters
swapped_string = my_string.swapcase()

print("Original string:", my_string) # Output: Python Programming
print("Swapped case string:", swapped_string) # Output: pYTHON pROGRAMMING
  1. count(): Returns the number of occurrences of a substring in a string. Here’s an example:
# Using count() to count occurrences of a substring
my_string = "Python programming is fun. Python is easy to learn."

# Count the occurrences of the word "Python"
count_python = my_string.count("Python")

print("Occurrences of 'Python':", count_python) # Output: 2
  1. find(): Returns the index of the first occurrence of a substring in a string. Here’s an example:
# Using find() to find the index of the first occurrence of a substring
my_string = "Python programming is fun."

# Find the index of the first occurrence of "Python"
index_python = my_string.find("Python")

print("Index of 'Python':", index_python) # Output: 0
  1. rfind(): Returns the index of the last occurrence of a substring in a string.
# Using rfind() to find the index of the last occurrence of a substring
my_string = "Python programming is fun. Python is easy to learn."

# Find the index of the last occurrence of "Python"
last_index_python = my_string.rfind("Python")

print("Index of the last 'Python':", last_index_python) # Output: 36

       10. index(): Like `find()`, but raises a `ValueError` if the substring is not found.

# Using index() to find the index of the first occurrence of a substring
my_string = "Python programming is fun."

# Find the index of the first occurrence of "Python"
index_python = my_string.index("Python")

print("Index of 'Python':", index_python) # Output: 0
  1. rindex(): Like `rfind()`, but raises a `ValueError` if the substring is not found.
# Using rindex() to find the index of the last occurrence of a substring
my_string = "Python programming is fun. Python is easy to learn."

# Find the index of the last occurrence of "Python"
last_index_python = my_string.rindex("Python")

print("Index of the last 'Python':", last_index_python) # Output: 36
  1. startswith(): Returns `True` if a string starts with a specified prefix, otherwise `False`.
# Using startswith() to check if the string starts with a specified prefix
my_string = "Python programming is fun."

# Check if the string starts with "Python"
starts_with_python = my_string.startswith("Python")

print("Starts with 'Python':", starts_with_python) # Output: True
  1. endswith(): Returns `True` if a string ends with a specified suffix, otherwise `False`.
# Using endswith() to check if the string ends with a specified suffix
my_string = "Python programming is fun."

# Check if the string ends with "fun."
ends_with_fun = my_string.endswith("fun.")

print("Ends with 'fun.':", ends_with_fun) # Output: True
  1. replace(): Replaces all occurrences of a substring with another substring.
# Using replace() to replace all occurrences of a substring
my_string = "Python is fun. Python is awesome."

# Replace "Python" with "Java"
new_string = my_string.replace("Python", "Java")

print("Original string:", my_string)
print("New string:", new_string)

       15. strip(): Removes whitespace (or other characters) from the beginning and end of a string.

  1. rstrip(): Removes whitespace (or other characters) from the end of a string.
# Using rstrip() to remove trailing whitespace
my_string = "Hello, world! "

# Remove the trailing whitespace
new_string = my_string.rstrip()

print("Original string:", repr(my_string))
print("New string:", repr(new_string))
  1. lstrip(): Removes whitespace (or other characters) from the beginning of a string.
# Using lstrip() to remove leading whitespace
my_string = " Hello, world!"

# Remove the leading whitespace
new_string = my_string.lstrip()

print("Original string:", repr(my_string))
print("New string:", repr(new_string))
  1. split(): Splits a string into a list of substrings using a specified delimiter.
# Using split() to split a string into a list of words
my_string = "Python is fun!"

# Split the string by whitespace (default behavior)
words = my_string.split()

print("List of words:", words)
  1. rsplit(): Splits a string from the right into a list of substrings using a specified delimiter.
# Using rsplit() to split a string from the right
my_string = "Python is fun and Python is awesome"

# Split the string by whitespace from the right
words = my_string.rsplit()

print("List of words:", words)

The separator used is the comma, which is passed as an argument to the rsplit() method. The 1 argument is used to specify that only one splitting should occur, which in this case splits the last fruit from the first two. Finally, we print the list of fruits.

       20. join(): Joins a list of strings into a single string using a specified delimiter.

# Joining with a space
words = ["Hello", "world", "Python", "rocks!"]
sentence = " ".join(words)
print(sentence) # Output: "Hello world Python rocks!"

# Joining with a comma
items = ["apple", "banana", "cherry"]
csv_line = ",".join(items)
print(csv_line) # Output: "apple,banana,cherry"

# Joining with a hyphen
letters = ["a", "b", "c", "d"]
hyphenated = "-".join(letters)
print(hyphenated) # Output: "a-b-c-d"
  1. isalnum(): Returns `True` if a string contains only alphanumeric characters, otherwise `False`.
# String with only letters and numbers
print("Hello123".isalnum()) # Output: True

# String with only letters
print("Python".isalnum()) # Output: True

# String with only numbers
print("2024".isalnum()) # Output: True

# String with a space
print("Hello World".isalnum()) # Output: False

# String with special characters
print("Hello@123".isalnum()) # Output: False

# Empty string
print("".isalnum()) # Output: False
  1. isalpha(): Returns `True` if a string contains only alphabetic characters, otherwise `False`.
# String with only letters
print("Python".isalpha()) # Output: True

# String with a space
print("Hello World".isalpha()) # Output: False

# String with numbers
print("Python3".isalpha()) # Output: False

# String with special characters
print("Hello!".isalpha()) # Output: False

# Empty string
print("".isalpha()) # Output: False
  1. isdigit() : Returns `True` if a string contains only digits; otherwise, `False`.
# String with only digits
print("12345".isdigit()) # Output: True

# String with letters
print("123abc".isdigit()) # Output: False

# String with a space
print("123 456".isdigit()) # Output: False

# Empty string
print("".isdigit()) # Output: False
  1. islower(): Returns `True` if all characters in a string are lowercase, otherwise `False`.
# All lowercase letters
print("hello".islower()) # Output: True

# Mixed case
print("Hello".islower()) # Output: False

# Digits and special characters are ignored
print("hello123!".islower()) # Output: True

# Contains an uppercase letter
print("helloWorld".islower()) # Output: False

# Empty string
print("".islower()) # Output: False

      25. isupper(): Returns `True` if all characters in a string are uppercase, otherwise `False`.

# All uppercase letters
print("HELLO".isupper()) # Output: True

# Mixed case
print("Hello".isupper()) # Output: False

# Includes digits and symbols
print("HELLO123!".isupper()) # Output: True

# Contains a lowercase letter
print("HELLOworld".isupper()) # Output: False

# Empty string
print("".isupper()) # Output: False
  1. istitle(): Returns `True` if a string is titlecased (i.e., the first character of each word is capitalized), otherwise `False`.
# Title-cased string
print("Hello World".istitle()) # Output: True

# Mixed case
print("Hello world".istitle()) # Output: False

# Uppercase string
print("HELLO WORLD".istitle()) # Output: False

# Lowercase string
print("hello world".istitle()) # Output: False

# Single title-cased word
print("Python".istitle()) # Output: True
  1. isspace(): Returns `True` if a string contains only whitespace characters, otherwise `False`.
# Only spaces
print(" ".isspace()) # Output: True

# Only tabs
print("\t\t".isspace()) # Output: True

# Newline characters
print("\n\n".isspace()) # Output: True

# Mixed whitespace characters
print(" \t\n".isspace()) # Output: True

# Empty string
print("".isspace()) # Output: False
  1. maketrans(): Creates a translation table to be used with the `translate()` function.
# Mapping characters 'a' -> '1', 'b' -> '2', 'c' -> '3'
trans_table = str.maketrans("abc", "123")
text = "abcde"
print(text.translate(trans_table)) # Output: "123de"
  1. translate(): Returns a copy of a string with specified characters replaced.
# Create a translation table to replace 'a' -> '1', 'b' -> '2', 'c' -> '3'
trans_table = str.maketrans("abc", "123")
text = "abcde"
print(text.translate(trans_table)) # Output: "123de"
  1. zfill(): Pads a numeric string with zeros on the left until the specified width is reached.
# Example using zfill() to pad a string with leading zeros
number = "42"
padded_number = number.zfill(5)

print(padded_number) # Output: '00042'
  1. expandtabs(): Replaces tabs in a string with spaces.
# Example using expandtabs() to replace tabs with spaces
text = "Hello\tWorld\tPython"
expanded_text = text.expandtabs(4) # Replaces tabs with 4 spaces

print(expanded_text)

# Output:
'Hello World Python'
  1. encode(): Encodes a string using a specified encoding.
# Encoding a string using UTF-8
my_string = "hello world"
encoded_string = my_string.encode("utf-8")

print(encoded_string) # Output: b'hello world'

  1. format_map(): Formats a string using a dictionary.
# Using format_map() to format a string using a dictionary
person = {"name": "Alice", "age": 30}

# Format string using the dictionary
formatted_string = "My name is {name} and I am {age} years old.".format_map(person)

print(formatted_string)
  1. isdecimal(): Returns `True` if a string contains only decimal characters, otherwise `False`.
# Using isdecimal() to check if a string contains only decimal characters
my_string = "12345"

# Check if the string contains only decimal characters
is_decimal = my_string.isdecimal()

print(is_decimal) # Output: True
  1. isnumeric(): Returns `True` if a string contains only numeric characters, otherwise `False`.
# Using isnumeric() to check if a string contains only numeric characters
my_string = "12345"

# Check if the string is numeric
is_numeric = my_string.isnumeric()

print(is_numeric) # Output: True
  1. partition(): It splits the string into parts based on the first occurrence of the substring.
# Using partition() to split a string based on the first occurrence of a substring
my_string = "Hello, world! Welcome to Python."

# Split the string at the first occurrence of ","
result = my_string.partition(",")

print(result)

Python List Comprehension

List comprehension is a concise and expressive way of creating lists in Python. It allows you to define a list and its elements within a single line of code, combining the functionality of a for loop, optional if conditions, and even nested loops. List comprehensions are preferred for their readability and efficiency compared to traditional for loops.

  1. Simple For Loop List Comprehension:

This type of list comprehension is used to create a list by iterating over elements from an iterable (e.g., list, tuple, string) without any filtering or conditions. Here’s an example:

# Example list
numbers = [1, 2, 3, 4, 5]

# List comprehension without filtering or conditions
new_list = [num for num in numbers]

print(new_list) # Output: [1, 2, 3, 4, 5]
  1. Loop and If Condition List Comprehension:

This type of list comprehension includes an if condition to filter elements while iterating over the iterable. Only elements that satisfy the condition are included in the new list. Here’s an example:

# Example list
numbers = [1, 2, 3, 4, 5, 6]

# List comprehension with an if condition
even_numbers = [num for num in numbers if num % 2 == 0]

print(even_numbers) # Output: [2, 4, 6]
  1. Loop and If-Else Condition List Comprehension:
# Example list
numbers = [1, 2, 3, 4, 5, 6]

# List comprehension with if-else condition
result = ['Even' if num % 2 == 0 else 'Odd' for num in numbers]

print(result)
# Output: ['Odd', 'Even', 'Odd', 'Even', 'Odd', 'Even']

This type of list comprehension allows you to perform different operations based on the if-else condition while iterating over the iterable. Here’s an example:

  1. Nested Loop List Comprehension:

This type of list comprehension allows you to use nested loops for creating more complex lists by iterating over multiple iterables simultaneously. Here’s an example:

# Example nested loops
colors = ['red', 'green', 'blue']
objects = ['ball', 'box', 'pen']

# List comprehension with nested loops
combinations = [f"{color} {obj}" for color in colors for obj in objects]

print(combinations)
# Output: ['red ball', 'red box', 'red pen', 'green ball', 'green box', 'green pen', 'blue ball', 'blue box', 'blue pen']

Shallow Copy and Deep Copy of Lists in Python:

In Python, when dealing with lists or other compound data structures, understanding the concepts of shallow copy and deep copy is crucial. Both concepts involve creating a new copy of an existing list, but they differ in how they handle nested objects within the list.

  1. Shallow Copy:

A shallow copy of a list creates a new list object but does not create copies of the elements inside the list. Instead, it copies references to the original objects. This means that changes made to nested objects within the copied list will affect the original list, and vice versa. To perform a shallow copy, you can use the `copy()` method or the slicing notation `[:]`. Example of Shallow Copy:

import copy

# Original list with a nested list
original_list = [[1, 2, 3], [4, 5, 6]]

# Shallow copy using the copy() method
shallow_copy1 = original_list.copy()

# Shallow copy using slicing notation
shallow_copy2 = original_list[:]

# Modifying a nested element in the copied list
shallow_copy1[0][0] = 99

print("Original List:", original_list)
# Output: Original List: [[99, 2, 3], [4, 5, 6]]

print("Shallow Copy 1:", shallow_copy1)
# Output: Shallow Copy 1: [[99, 2, 3], [4, 5, 6]]

print("Shallow Copy 2:", shallow_copy2)
# Output: Shallow Copy 2: [[99, 2, 3], [4, 5, 6]]
  1. Deep Copy:

A deep copy of a list creates a new list object and also recursively creates copies of all the nested objects within the original list. In other words, the copied list and its nested objects are entirely independent of the original list and its nested objects. To perform a deep copy, you need to use the `deepcopy()` function from the `copy` module. Example of Deep Copy:

import copy

# Original list with nested lists
original_list = [[1, 2, 3], [4, 5, 6]]

# Creating a deep copy
deep_copy = copy.deepcopy(original_list)

# Modifying a nested element in the deep copy
deep_copy[0][0] = 99

print("Original List:", original_list)
# Output: Original List: [[1, 2, 3], [4, 5, 6]]

print("Deep Copy:", deep_copy)
# Output: Deep Copy: [[99, 2, 3], [4, 5, 6]]

Shallow copy is faster and suitable when you want to create a new list but share references to nested objects. Deep copy is appropriate when you need an entirely independent copy of the original list and all its nested objects.

Python List Methods And Functions

Lists in Python have many built-in methods that you can use to modify or manipulate the list. Here are some of the most commonly used methods:

  1. append() – It is used to add an element to the end of the list.
# Define a list
my_list = ['apple', 'banana', 'cherry']

# Add an element to the end of the list
my_list.append('date')

# Print the updated list
print(my_list)
  1. extend() – It is used to add the elements of another list to the end of the list or to combine two lists.
# Define a list
my_list = ['apple', 'banana']

# Extend the list with a tuple
my_list.extend(('cherry', 'date'))

# Extend the list with a set

my_list.extend({'elderberry', 'fig'})

# Print the updated list
print(my_list)
  1. insert() – It is used to insert an element at a specific position in the list.
# Define a list
my_list = ['apple', 'banana', 'cherry']

# Insert an element at index 1
my_list.insert(1, 'date')

# Print the updated list
print(my_list)

         4. remove() – It is used to remove the first occurrence of an element from the list.

# Define a list
my_list = ['apple', 'banana', 'cherry', 'banana']

# Remove the first occurrence of 'banana'
my_list.remove('banana')

# Print the updated list
print(my_list)
  1. pop() – It removes and returns the element at a specific position in the list.
# Define a list
my_list = ['apple', 'banana', 'cherry']

# Remove and return the last element
removed_element = my_list.pop()

# Print the updated list and the removed element
print("Updated list:", my_list)
print("Removed element:", removed_element)

 6. sort() – It sorts the elements of the list in ascending order.

# Define a list of numbers
my_list = [3, 1, 4, 1, 5, 9, 2, 6]

# Sort the list in descending order
my_list.sort(reverse=True)

# Print the sorted list
print(my_list)

    7 .reverse() – It reverses the order of the elements in the list.

# Define a list of strings
my_list = ['apple', 'banana', 'cherry']

# Reverse the list
my_list.reverse()

# Print the reversed list
print(my_list)
  1. clear() – It is used to remove all elements from a list, effectively emptying the list. After the clear() function is applied to a list, the list becomes empty with a length of 0.
# Define a list of strings
my_list = ['apple', 'banana', 'cherry']

# Clear all elements from the list
my_list.clear()

# Print the cleared list
print(my_list)
  1. copy() – It creates a shallow copy of a list. The shallow copy means that a new list is created with the same elements as the original list, but the elements themselves are not duplicated. Any changes made to the elements in the copied list will also affect the original list, and vice versa.
# Define a list with immutable elements
my_list = [1, 2, 3]

# Create a shallow copy of the list
my_list_copy = my_list.copy()

# Modify the copied list
my_list_copy.append(4)

# Print both lists
print("Original list:", my_list)
print("Copied list:", my_list_copy)
  1. index() – It is used to find the index of the first occurrence of a specified element within a list. If the element is not found in the list, it raises a ValueError.
# Define a list
my_list = ['apple', 'banana', 'cherry']

# Find the index of 'banana'
index_of_banana = my_list.index('banana')

# Print the index
print("Index of 'banana':", index_of_banana)
  1. count() – It is used to count the number of occurrences of a specified element in a list.
# Define a list
my_list = ['apple', 'banana', 'cherry', 'banana', 'apple']

# Count occurrences of 'banana'
banana_count = my_list.count('banana')

# Print the count
print("Count of 'banana':", banana_count)

Updating list values:

Lists in Python are mutable, and their values can be updated by using the slice and assignment the ( = ) operator.

# Define a list
my_list = [1, 2, 3, 4, 5]

# Remove elements at indices 1 to 3
my_list[1:4] = []

# Print the updated list
print(my_list)

Built-in function for Python lists:

Here are some examples of commonly used built-in functions for lists in Python:

  1. len(): It returns the length of the list.
# Define a list
my_list = [1, 2, 3, 4, 5]

# Get the length of the list
list_length = len(my_list)

# Print the length
print("Length of the list:", list_length)
  1. max(): It returns the largest element in the list.
# Define a list of numbers
my_list = [1, 5, 3, 9, 2]

# Get the largest element in the list
largest_element = max(my_list)

# Print the largest element

print("Largest element:", largest_element)
  1. min(): It returns the smallest element in the list.
# Define a list of numbers
my_list = [10, 5, 20, 3, 8]

# Get the smallest element in the list
smallest_element = min(my_list)

# Print the smallest element
print("Smallest element:", smallest_element)
  1. sum(): It returns the sum of all elements in the list.
# Define a list of numbers
my_list = [1, 2, 3, 4, 5]

# Calculate the sum of the elements in the list
total_sum = sum(my_list)

# Print the total sum
print("Sum of the list:", total_sum)

       5. sorted(): It returns a new sorted list.

# Define a list of numbers
my_list = [5, 2, 9, 1, 5, 6]

# Get a new sorted list
sorted_list = sorted(my_list)

# Print the sorted list
print("Sorted list:", sorted_list)
  1. list(): It converts an iterable to a list.
# Define a tuple
my_tuple = (1, 2, 3, 4)

# Convert the tuple to a list
my_list = list(my_tuple)

# Print the list
print("List from tuple:", my_list)

       7. any(): It returns True if at least one element in the list is True.

# Define a list of Boolean values
my_list = [False, False, True, False]

# Check if any element is True
result = any(my_list)

# Print the result
print("Any True in the list:", result)
  1. all(): It returns True if all elements in the list are True.
# Define a list of Boolean values
my_list = [True, True, True]

# Check if all elements are True
result = all(my_list)

# Print the result
print("All True in the list:", result)

        9. enumerate(): It returns an iterator that contains tuples of (index, element) pairs.

# Define a list
my_list = ['apple', 'banana', 'cherry']

# Use enumerate to get index and element
for index, element in enumerate(my_list):
print(f"Index: {index}, Element: {element}")

# Output:

Index: 0, Element: apple
Index: 1, Element: banana
Index: 2, Element: cherry
  1. zip(): It returns an iterator that aggregates elements from multiple lists into tuples.
# Define two lists
list1 = [1, 2, 3]
list2 = ['a', 'b', 'c']

# Use zip to combine them into tuples
zipped = zip(list1, list2)

# Convert the zipped object to a list and print
zipped_list = list(zipped)
print(zipped_list)

        11.reversed(): It returns a reverse iterator that can be used to iterate over a list in reverse order.

# Define a list
my_list = [1, 2, 3, 4, 5]

# Convert the reversed iterator to a list
reversed_list = list(reversed(my_list))

# Print the reversed list
print(reversed_list)

SQL REVOKE Statement

SQL Tutorial › Security › REVOKE

SQL REVOKE Statement

IntermediateLesson 68 of 697 min read5 queries run on MySQL 8.4

REVOKE is the opposite of GRANT: it takes privileges away from an account or role. You need it when someone changes team, when an application no longer needs write access, or after an audit finds an account with too much power. The privileges are removed immediately.

In this lesson
  • Revoke individual privileges and all privileges
  • Revoke roles and the grant option
  • Verify with SHOW GRANTS
  • Avoid the “revoke at a different level” trap

REVOKE syntax

MySQL
REVOKE privilege [, privilege ...]ON levelFROM 'user'@'host';

The level must match how the privilege was granted (company.*, company.orders, …).

Revoke one privilege

The shop application should no longer delete rows (it uses soft deletes now):

MySQL
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p#App2026';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'shop_app'@'localhost';REVOKE DELETE ON company.* FROM 'shop_app'@'localhost';SHOW GRANTS FOR 'shop_app'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p
Query OK, 0 rows affected
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'shop_app'@'local…
Query OK, 0 rows affected
mysql> REVOKE DELETE ON company.* FROM 'shop_app'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'shop_app'@'localhost'
Grants for shop_app@localhost
GRANT USAGE ON *.* TO `shop_app`@`localhost`
GRANT SELECT, INSERT, UPDATE ON `company`.* TO `shop_app`@`localhost`
2 rows in set
How this query works, step by step
  1. The account starts with four privileges on company.*.
  2. REVOKE DELETE removes one; SHOW GRANTS now lists SELECT, INSERT, UPDATE.
  3. The change applies to new statements immediately, even in sessions that are already connected.

Revoke everything

MySQL
CREATE USER 'contractor'@'localhost' IDENTIFIED BY 'C0ntract#2026';GRANT SELECT, INSERT ON company.* TO 'contractor'@'localhost';GRANT SELECT ON company.employees TO 'contractor'@'localhost';REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'contractor'@'localhost';SHOW GRANTS FOR 'contractor'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'contractor'@'localhost' IDENTIFIED BY 'C0ntract
Query OK, 0 rows affected
mysql> GRANT SELECT, INSERT ON company.* TO 'contractor'@'localhost'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.employees TO 'contractor'@'localhost'
Query OK, 0 rows affected
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'contractor'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'contractor'@'localhost'
Grants for contractor@localhost
GRANT USAGE ON *.* TO `contractor`@`localhost`
1 row in set
How this query works, step by step
  1. The contractor had privileges at two levels: database and table.
  2. REVOKE ALL PRIVILEGES, GRANT OPTION FROM ... (without ON) removes them at every level.
  3. Only USAGE remains: the account can log in but do nothing. To remove the account completely, DROP USER.

The level trap

A privilege granted at database level cannot be revoked for one table:

MySQL
CREATE USER 'analyst1'@'localhost' IDENTIFIED BY 'An@lyst2026';GRANT SELECT ON company.* TO 'analyst1'@'localhost';REVOKE SELECT ON company.employees FROM 'analyst1'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'analyst1'@'localhost' IDENTIFIED BY 'An@lyst2026'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.* TO 'analyst1'@'localhost'
Query OK, 0 rows affected
mysql> REVOKE SELECT ON company.employees FROM 'analyst1'@'localhost'
ERROR 1147 (42000): There is no such grant defined for user 'analyst1' on host 'localhost' on table 'employees'
How this query works, step by step
  1. SELECT was granted on company.*, not on company.employees.
  2. There is no table-level grant to remove, so MySQL reports error 1147.
  3. To give “all tables except employees”, revoke the database grant and grant SELECT table by table (or through views).

Revoke a role

MySQL
CREATE ROLE 'analyst';GRANT SELECT ON company.* TO 'analyst';CREATE USER 'priya'@'localhost' IDENTIFIED BY 'Pr1ya#2026';GRANT 'analyst' TO 'priya'@'localhost';REVOKE 'analyst' FROM 'priya'@'localhost';SHOW GRANTS FOR 'priya'@'localhost';
ResultMySQL 8.4
mysql> CREATE ROLE 'analyst'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.* TO 'analyst'
Query OK, 0 rows affected
mysql> CREATE USER 'priya'@'localhost' IDENTIFIED BY 'Pr1ya
Query OK, 0 rows affected
mysql> GRANT 'analyst' TO 'priya'@'localhost'
Query OK, 0 rows affected
mysql> REVOKE 'analyst' FROM 'priya'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'priya'@'localhost'
Grants for priya@localhost
GRANT USAGE ON *.* TO `priya`@`localhost`
1 row in set

Changing the role itself (REVOKE INSERT ON company.* FROM 'developer';) changes the access of every account that has the role.

Revoke the grant option

MySQL
CREATE USER 'team_lead'@'localhost' IDENTIFIED BY 'L3ad#2026';GRANT SELECT ON company.* TO 'team_lead'@'localhost' WITH GRANT OPTION;REVOKE GRANT OPTION ON company.* FROM 'team_lead'@'localhost';SHOW GRANTS FOR 'team_lead'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'team_lead'@'localhost' IDENTIFIED BY 'L3ad
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.* TO 'team_lead'@'localhost' WITH GRANT OPTION
Query OK, 0 rows affected
mysql> REVOKE GRANT OPTION ON company.* FROM 'team_lead'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'team_lead'@'localhost'
Grants for team_lead@localhost
GRANT USAGE ON *.* TO `team_lead`@`localhost`
GRANT SELECT ON `company`.* TO `team_lead`@`localhost`
2 rows in set

The team lead keeps SELECT but can no longer give it to others.

Quick quiz

1. How do you remove all privileges of an account at every level?

2. SELECT was granted ON company.*. What happens with REVOKE SELECT ON company.orders?

3. Does a REVOKE affect sessions that are already connected?

REVOKE FAQ

What is the difference between GRANT and REVOKE?

GRANT gives privileges to an account or role; REVOKE removes them.

How do I remove all privileges from a MySQL user?

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; The account keeps only USAGE. Use DROP USER to delete it.

Why does REVOKE give error 1141 or 1147?

The privilege was not granted at the level you named. Check with SHOW GRANTS and revoke at the same level (db.*, db.table or global).

SQL GRANT Statement

SQL Tutorial › Security › GRANT

SQL GRANT Statement

IntermediateLesson 67 of 6910 min read7 queries run on MySQL 8.4

GRANT gives an account (or a role) permission to do something: read a table, insert rows, create tables, run a procedure. It is the core of DCL (Data Control Language). Granting exactly what each account needs, and nothing more, is the most effective database security measure you can take.

In this lesson
  • Grant privileges at global, database, table and column level
  • Inspect privileges with SHOW GRANTS
  • Grant through views for row and column security
  • Understand WITH GRANT OPTION and ALL PRIVILEGES

GRANT syntax

MySQL
GRANT privilege [, privilege ...]ON levelTO 'user'@'host' [, ...][WITH GRANT OPTION];
LevelWritten asExample
Global.every database on the server
Databasecompany.*every table in company
Tablecompany.ordersone table
ColumnSELECT (col1, col2) ON company.employeessome columns of one table
RoutineEXECUTE ON PROCEDURE company.place_orderone procedure

Common privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, EXECUTE, CREATE VIEW, TRIGGER, and ALL PRIVILEGES.

Database-level grant

MySQL
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p#App2026';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'shop_app'@'localhost';SHOW GRANTS FOR 'shop_app'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p
Query OK, 0 rows affected
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'shop_app'@'local…
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'shop_app'@'localhost'
Grants for shop_app@localhost
GRANT USAGE ON *.* TO `shop_app`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `company`.* TO `shop_app`@`localhost`
2 rows in set
How this query works, step by step
  1. The account can read and change rows in every table of company.
  2. It cannot create, alter or drop tables, and it cannot touch other databases.
  3. SHOW GRANTS lists exactly what the account may do.

Table-level grant

A reporting account that may read only the shop tables:

MySQL
CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt#2026';GRANT SELECT ON company.orders TO 'report_reader'@'localhost';GRANT SELECT ON company.order_items TO 'report_reader'@'localhost';GRANT SELECT ON company.products TO 'report_reader'@'localhost';SHOW GRANTS FOR 'report_reader'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.orders TO 'report_reader'@'localhost'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.order_items TO 'report_reader'@'localhost'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.products TO 'report_reader'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'report_reader'@'localhost'
Grants for report_reader@localhost
GRANT USAGE ON *.* TO `report_reader`@`localhost`
GRANT SELECT ON `company`.`order_items` TO `report_reader`@`localhost`
GRANT SELECT ON `company`.`orders` TO `report_reader`@`localhost`
GRANT SELECT ON `company`.`products` TO `report_reader`@`localhost`
4 rows in set

Column-level grant

HR assistants may see employee names and departments, but not salaries:

MySQL
CREATE USER 'hr_assistant'@'localhost' IDENTIFIED BY 'Hr#Asst2026';GRANT SELECT (emp_id, first_name, last_name, dept_id, job_title) ON company.employees TO 'hr_assistant'@'localhost';GRANT UPDATE (job_title) ON company.employees TO 'hr_assistant'@'localhost';SHOW GRANTS FOR 'hr_assistant'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'hr_assistant'@'localhost' IDENTIFIED BY 'Hr
Query OK, 0 rows affected
mysql> GRANT SELECT (emp_id, first_name, last_name, dept_id, job_title) ON co…
Query OK, 0 rows affected
mysql> GRANT UPDATE (job_title) ON company.employees TO 'hr_assistant'@'local…
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'hr_assistant'@'localhost'
Grants for hr_assistant@localhost
GRANT USAGE ON *.* TO `hr_assistant`@`localhost`
GRANT SELECT (`dept_id`, `emp_id`, `first_name`, `job_title`, `last_name`), UPDATE (`job_title`) ON `company`.`employees` TO `hr_assistant`@`localhost`
2 rows in set
How this query works, step by step
  1. SELECT (columns) allows reading only the listed columns; SELECT salary FROM employees would fail for this account.
  2. UPDATE (job_title) allows changing that one column only.

Security through views

Column grants cannot filter rows. A view can: give a regional manager only Mumbai employees:

MySQL
CREATE VIEW mumbai_staff ASSELECT emp_id, first_name, last_name, job_title FROM employees WHERE city = 'Mumbai';CREATE USER 'mumbai_mgr'@'localhost' IDENTIFIED BY 'Mumb@i#2026';GRANT SELECT ON company.mumbai_staff TO 'mumbai_mgr'@'localhost';SHOW GRANTS FOR 'mumbai_mgr'@'localhost';
ResultMySQL 8.4
mysql> CREATE VIEW mumbai_staff AS
Query OK, 0 rows affected
mysql> CREATE USER 'mumbai_mgr'@'localhost' IDENTIFIED BY 'Mumb@i
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.mumbai_staff TO 'mumbai_mgr'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'mumbai_mgr'@'localhost'
Grants for mumbai_mgr@localhost
GRANT USAGE ON *.* TO `mumbai_mgr`@`localhost`
GRANT SELECT ON `company`.`mumbai_staff` TO `mumbai_mgr`@`localhost`
2 rows in set

The account can read the view but has no privilege on employees itself (Views).

Grant to a role

MySQL
CREATE ROLE 'qa_tester';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'qa_tester';CREATE USER 'arjun'@'localhost' IDENTIFIED BY 'Arj#un2026';GRANT 'qa_tester' TO 'arjun'@'localhost';SHOW GRANTS FOR 'arjun'@'localhost';
ResultMySQL 8.4
mysql> CREATE ROLE 'qa_tester'
Query OK, 0 rows affected
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'qa_tester'
Query OK, 0 rows affected
mysql> CREATE USER 'arjun'@'localhost' IDENTIFIED BY 'Arj
Query OK, 0 rows affected
mysql> GRANT 'qa_tester' TO 'arjun'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'arjun'@'localhost'
Grants for arjun@localhost
GRANT USAGE ON *.* TO `arjun`@`localhost`
GRANT `qa_tester`@`%` TO `arjun`@`localhost`
2 rows in set

See Users & Roles.

WITH GRANT OPTION and ALL PRIVILEGES

  • WITH GRANT OPTION lets the account pass its own privileges on to others. Give it only to administrators.
  • GRANT ALL PRIVILEGES ON company.* TO ... gives every database-level privilege, including DROP. Convenient for a developer’s own test database, dangerous for an application account.
MySQL
CREATE USER 'team_lead'@'localhost' IDENTIFIED BY 'L3ad#2026';GRANT ALL PRIVILEGES ON company.* TO 'team_lead'@'localhost' WITH GRANT OPTION;SHOW GRANTS FOR 'team_lead'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'team_lead'@'localhost' IDENTIFIED BY 'L3ad
Query OK, 0 rows affected
mysql> GRANT ALL PRIVILEGES ON company.* TO 'team_lead'@'localhost' WITH GRAN…
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'team_lead'@'localhost'
Grants for team_lead@localhost
GRANT USAGE ON *.* TO `team_lead`@`localhost`
GRANT ALL PRIVILEGES ON `company`.* TO `team_lead`@`localhost` WITH GRANT OPTION
2 rows in set

Errors

Granting on an account that does not exist fails in MySQL 8 (older versions silently created it):

MySQL
GRANT SELECT ON company.* TO 'ghost'@'localhost';
ResultMySQL 8.4
ERROR 1410 (42000): You are not allowed to create a user with GRANT

Quick quiz

1. What does ON company.* mean?

2. How do you let an account read only some columns of a table?

3. Which tool restricts which rows an account can see?

4. What does WITH GRANT OPTION allow?

GRANT FAQ

What is the GRANT command in SQL?

A Data Control Language statement that gives privileges, such as SELECT or INSERT, on databases, tables, columns or routines to accounts or roles.

How do I see the privileges of a MySQL user?

SHOW GRANTS FOR 'user'@'host'; For your own account, SHOW GRANTS;.

Do I need FLUSH PRIVILEGES after GRANT?

No. GRANT, REVOKE, CREATE USER and similar statements take effect immediately. FLUSH PRIVILEGES is only needed after editing the grant tables directly, which you should not do.

SQL DROP TABLE and DROP DATABASE

SQL Tutorial › Database Design › DROP TABLE & DATABASE

SQL DROP TABLE and DROP DATABASE

BeginnerLesson 58 of 697 min read7 queries run on MySQL 8.4

DROP removes a database object completely: the table’s structure, data, indexes and triggers, or a whole database with every table in it. It is instant and cannot be undone with ROLLBACK, so it is the one command where a typo hurts most. This lesson shows how to drop safely.

In this lesson
  • Remove a table with DROP TABLE
  • Make scripts re-runnable with IF EXISTS
  • Drop tables that are referenced by foreign keys
  • Drop a database, and compare DROP, TRUNCATE and DELETE

DROP TABLE

MySQL
CREATE TABLE temp_report (id INT, total INT);SHOW TABLES LIKE 'temp%';DROP TABLE temp_report;SHOW TABLES LIKE 'temp%';
ResultMySQL 8.4
mysql> CREATE TABLE temp_report (id INT, total INT)
Query OK, 0 rows affected
mysql> SHOW TABLES LIKE 'temp%'
Tables_in_company (temp%)
temp_report
1 row in set
mysql> DROP TABLE temp_report
Query OK, 0 rows affected
mysql> SHOW TABLES LIKE 'temp%'
Empty set
How this query works, step by step
  1. The table is created and SHOW TABLES lists it.
  2. DROP TABLE removes the table and all its rows.
  3. The second SHOW TABLES returns an Empty set.

IF EXISTS

Dropping a table that does not exist is an error:

MySQL
DROP TABLE no_such_table;
ResultMySQL 8.4
ERROR 1051 (42S02): Unknown table 'company.no_such_table'

IF EXISTS turns it into a note, so setup scripts can run again and again:

MySQL
DROP TABLE IF EXISTS no_such_table;SHOW WARNINGS;
ResultMySQL 8.4
mysql> DROP TABLE IF EXISTS no_such_table
Query OK, 0 rows affected, 1 warning
mysql> SHOW WARNINGS
LevelCodeMessage
Note1051Unknown table 'company.no_such_table'
1 row in set

Several tables at once

MySQL
CREATE TABLE t1 (a INT);CREATE TABLE t2 (a INT);DROP TABLE IF EXISTS t1, t2;SHOW TABLES LIKE 't_';
ResultMySQL 8.4
mysql> CREATE TABLE t1 (a INT)
Query OK, 0 rows affected
mysql> CREATE TABLE t2 (a INT)
Query OK, 0 rows affected
mysql> DROP TABLE IF EXISTS t1, t2
Query OK, 0 rows affected
mysql> SHOW TABLES LIKE 't_'
Empty set

Tables referenced by foreign keys

A parent table cannot be dropped while a child table references it:

MySQL
DROP TABLE customers;
ResultMySQL 8.4
ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'orders_ibfk_1' on table 'orders'.

Drop the child first, then the parent:

MySQL
DROP TABLE order_items;DROP TABLE orders;DROP TABLE customers;SHOW TABLES;
ResultMySQL 8.4
mysql> DROP TABLE order_items
Query OK, 0 rows affected
mysql> DROP TABLE orders
Query OK, 0 rows affected
mysql> DROP TABLE customers
Query OK, 0 rows affected
mysql> SHOW TABLES
Tables_in_company
departments
employees
products
3 rows in set
How this query works, step by step
  1. order_items references orders, and orders references customers.
  2. Dropping in child-to-parent order satisfies every foreign key.
  3. The three tables are gone; departments, employees and products remain.
Watch out

You may see scripts that run SET FOREIGN_KEY_CHECKS = 0; to drop tables in any order. That also lets broken data in, so use it only in throw-away development databases and switch it back on immediately.

DROP DATABASE

MySQL
CREATE DATABASE scratch_db;DROP DATABASE scratch_db;DROP DATABASE IF EXISTS scratch_db;SHOW DATABASES;
ResultMySQL 8.4
mysql> CREATE DATABASE scratch_db
Query OK, 1 row affected
mysql> DROP DATABASE scratch_db
Query OK, 0 rows affected
mysql> DROP DATABASE IF EXISTS scratch_db
Query OK, 0 rows affected, 1 warning
mysql> SHOW DATABASES
Database
information_schema
mysql
performance_schema
sys
4 rows in set
How this query works, step by step
  1. DROP DATABASE deletes the database and every table in it: Query OK, 0 rows affected (the count is the number of tables dropped).
  2. Running it again with IF EXISTS produces only a warning.

DROP vs TRUNCATE vs DELETE

DROP TABLETRUNCATE TABLEDELETE FROM
Removes datayesyes, all rowschosen rows
Removes structureyesnono
Can use WHEREnonoyes
Can be rolled backnonoyes
Fires DELETE triggersnonoyes

Safety checklist

  1. Run SELECT DATABASE(); to be sure which database you are in.
  2. Take a backup (mysqldump) of anything you might need.
  3. Prefer renaming (RENAME TABLE orders TO orders_old_2026) and dropping later, once nobody has complained.
  4. In production, grant the DROP privilege only to the accounts that need it (GRANT).

Quick quiz

1. What does DROP TABLE remove?

2. Why use DROP TABLE IF EXISTS in scripts?

3. In which order must you drop orders and customers?

DROP FAQ

What is the difference between DROP and TRUNCATE?

DROP TABLE deletes the table completely, structure included. TRUNCATE TABLE deletes all rows but keeps the empty table.

Can I undo DROP TABLE?

Not with ROLLBACK. Restore from a backup or point-in-time recovery using the binary log.

How do I drop a table with a foreign key?

Drop the referencing (child) tables first, or remove the foreign key with ALTER TABLE child DROP FOREIGN KEY name;.

SQL ALTER TABLE

SQL Tutorial › Database Design › ALTER TABLE

SQL ALTER TABLE

IntermediateLesson 57 of 6911 min read8 queries run on MySQL 8.4

Requirements change after a table is created: a new column for phone numbers, a longer name field, a renamed column, a new index. ALTER TABLE changes the structure of an existing table without losing its data. This lesson runs each kind of change on a copy of the sample customers table and shows the result with DESCRIBE.

In this lesson
  • Add, modify, rename and drop columns
  • Rename a table
  • Add and drop keys, indexes and constraints
  • Change large tables safely

Sample table

Every example works on a copy, so the original stays intact:

MySQL
CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;DESCRIBE clients;
ResultMySQL 8.4
mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…
Query OK, 7 rows affected
mysql> DESCRIBE clients
FieldTypeNullKeyDefaultExtra
customer_idintNONULL
customer_namevarchar(50)NONULL
emailvarchar(60)YESNULL
cityvarchar(30)YESNULL
4 rows in set

ADD COLUMN

MySQL
CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients  ADD COLUMN phone VARCHAR(15),  ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0 AFTER customer_name;SELECT customer_id, customer_name, loyalty_points, phone FROM clients LIMIT 3;
ResultMySQL 8.4
mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…
Query OK, 7 rows affected
mysql> ALTER TABLE clients
Query OK, 0 rows affected
mysql> SELECT customer_id, customer_name, loyalty_points, phone FROM clients …
customer_idcustomer_nameloyalty_pointsphone
1Ananya Sharma0NULL
2Rohit Kumar0NULL
3Fatima Khan0NULL
3 rows in set
How this query works, step by step
  1. One ALTER TABLE can make several changes, separated by commas.
  2. phone is added at the end and is NULL for existing rows.
  3. loyalty_points is NOT NULL DEFAULT 0, so every existing row gets 0; AFTER customer_name places it right after that column (FIRST puts it first).

MODIFY COLUMN: change type or constraints

MySQL
CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients MODIFY customer_name VARCHAR(100) NOT NULL;DESCRIBE clients;
ResultMySQL 8.4
mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…
Query OK, 7 rows affected
mysql> ALTER TABLE clients MODIFY customer_name VARCHAR(100) NOT NULL
Query OK, 7 rows affected
mysql> DESCRIBE clients
FieldTypeNullKeyDefaultExtra
customer_idintNONULL
customer_namevarchar(100)NONULL
emailvarchar(60)YESNULL
cityvarchar(30)YESNULL
4 rows in set
Watch out

MODIFY replaces the whole column definition. If the column had NOT NULL or a DEFAULT and you leave it out, it is gone. Always restate everything you want to keep.

Shrinking a column fails when existing data would not fit (in strict mode):

MySQL
CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients MODIFY customer_name VARCHAR(10);
ResultMySQL 8.4
mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…
Query OK, 7 rows affected
mysql> ALTER TABLE clients MODIFY customer_name VARCHAR(10)
ERROR 1265 (01000): Data truncated for column 'customer_name' at row 1

RENAME COLUMN and CHANGE

MySQL
CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients RENAME COLUMN customer_name TO full_name;ALTER TABLE clients CHANGE city home_city VARCHAR(40);SELECT customer_id, full_name, home_city FROM clients LIMIT 2;
ResultMySQL 8.4
mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…
Query OK, 7 rows affected
mysql> ALTER TABLE clients RENAME COLUMN customer_name TO full_name
Query OK, 0 rows affected
mysql> ALTER TABLE clients CHANGE city home_city VARCHAR(40)
Query OK, 0 rows affected
mysql> SELECT customer_id, full_name, home_city FROM clients LIMIT 2
customer_idfull_namehome_city
1Ananya SharmaMumbai
2Rohit KumarDelhi
2 rows in set
How this query works, step by step
  1. RENAME COLUMN old TO new only renames (MySQL 8.0+).
  2. CHANGE old new definition renames and redefines the column in one step (here VARCHAR(40)).

DROP COLUMN

MySQL
CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients DROP COLUMN email;SELECT * FROM clients LIMIT 2;
ResultMySQL 8.4
mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…
Query OK, 7 rows affected
mysql> ALTER TABLE clients DROP COLUMN email
Query OK, 0 rows affected
mysql> SELECT * FROM clients LIMIT 2
customer_idcustomer_namecity
1Ananya SharmaMumbai
2Rohit KumarDelhi
2 rows in set

The column and all its data are deleted permanently.

Rename a table

MySQL
CREATE TABLE clients AS SELECT customer_id, customer_name FROM customers;ALTER TABLE clients RENAME TO client_list;SHOW TABLES LIKE 'client%';
ResultMySQL 8.4
mysql> CREATE TABLE clients AS SELECT customer_id, customer_name FROM customers
Query OK, 7 rows affected
mysql> ALTER TABLE clients RENAME TO client_list
Query OK, 0 rows affected
mysql> SHOW TABLES LIKE 'client%'
Tables_in_company (client%)
client_list
1 row in set

RENAME TABLE a TO b; does the same and can rename several tables at once.

Keys, indexes and constraints

MySQL
CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients  ADD PRIMARY KEY (customer_id),  ADD UNIQUE KEY uq_client_email (email),  ADD INDEX idx_client_city (city),  ADD CONSTRAINT chk_name CHECK (customer_name <> '');SHOW INDEX FROM clients;
ResultMySQL 8.4
mysql> CREATE TABLE clients AS SELECT customer_id, customer_name, email, city…
Query OK, 7 rows affected
mysql> ALTER TABLE clients
Query OK, 7 rows affected
mysql> SHOW INDEX FROM clients
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
clients0PRIMARY1customer_idA7NULLNULLBTREEYESNULL
clients0uq_client_email1emailA7NULLNULLYESBTREEYESNULL
clients1idx_client_city1cityA7NULLNULLYESBTREEYESNULL
3 rows in set
How this query works, step by step
  1. CREATE TABLE ... AS SELECT copied data but no keys, so they are added now.
  2. One statement adds a primary key, a unique key, a normal index and a check.
  3. SHOW INDEX lists the three indexes; the check constraint is not an index.

Remove them with DROP PRIMARY KEY, DROP INDEX name, DROP CHECK name and DROP FOREIGN KEY name.

Changing big tables safely

  • Many changes in MySQL 8 are instant or in-place (adding a column at the end, renaming, adding an index) and do not block reads and writes for long.
  • Others (changing a column’s type) rebuild the table, copying every row. On a large table that can take a long time.
  • Ask for the cheap algorithm and let MySQL refuse if it cannot: ALTER TABLE big ADD COLUMN note VARCHAR(50), ALGORITHM=INSTANT;
  • Test schema changes on a copy, take a backup first, and run them in a quiet period.
  • ALTER TABLE is DDL: it commits any open transaction and cannot be rolled back.

Exercise: Extend products

On a copy of products called catalog, add a discount_pct INT NOT NULL DEFAULT 0 column after price, rename stock to units_in_stock, and show the structure.

Expected resultMySQL 8.4
mysql> CREATE TABLE catalog AS SELECT * FROM products
Query OK, 9 rows affected
mysql> ALTER TABLE catalog
Query OK, 0 rows affected
mysql> DESCRIBE catalog
FieldTypeNullKeyDefaultExtra
product_idintNONULL
product_namevarchar(50)NONULL
categoryvarchar(30)YESNULL
priceintYESNULL
discount_pctintNO0
units_in_stockintYESNULL
6 rows in set
Hint 1

Create the copy with CREATE TABLE catalog AS SELECT * FROM products;, then one ALTER TABLE with two changes.

Quick quiz

1. Which clause renames a column without changing its type?

2. What happens to a NOT NULL constraint you forget to repeat in MODIFY?

3. Can ALTER TABLE be rolled back inside a transaction?

ALTER TABLE FAQ

How do I add a column to an existing table in SQL?

ALTER TABLE table_name ADD COLUMN column_name datatype [constraints]; Use AFTER other_column or FIRST to choose its position in MySQL.

What is the difference between MODIFY and CHANGE in MySQL?

MODIFY redefines a column but keeps its name. CHANGE can rename it and redefine it at the same time.

How do I rename a table in MySQL?

ALTER TABLE old_name RENAME TO new_name; or RENAME TABLE old_name TO new_name;

Does ALTER TABLE lock the table?

It depends on the change. Instant and in-place changes allow normal work; changes that rebuild the table can block writes for their duration. Specify ALGORITHM and LOCK to control it.

SQL DELETE and TRUNCATE

SQL Tutorial › Modifying Data › DELETE & TRUNCATE

SQL DELETE and TRUNCATE

BeginnerLesson 20 of 6910 min read9 queries run on MySQL 8.4

DELETE removes rows from a table; TRUNCATE empties a table completely. Both are permanent once committed, so the habits from the UPDATE lesson matter even more here: check the WHERE with a SELECT first, and use a transaction when the data is valuable.

In this lesson
  • Delete rows that match a condition
  • Delete using a subquery or a join
  • Understand foreign key errors on delete
  • Choose between DELETE, TRUNCATE and DROP

DELETE syntax

MySQL
DELETE FROM table_nameWHERE condition;
Watch out

DELETE FROM table_name; without WHERE removes every row. The table itself stays, empty.

Sample data

products · 9 rows
product_idproduct_namecategorypricestock
1Wireless MouseElectronics799120
2Mechanical KeyboardElectronics349945
3USB-C HubElectronics19990
4Office ChairFurniture899912
5Standing DeskFurniture189995
6Notebook PackStationery249300
7Gel PensStationery199250
8Desk LampFurniture149930
9Monitor ArmFurniture279918

Delete one row

Product 9 (Monitor Arm) has never been ordered and is discontinued:

SQL
DELETE FROM productsWHERE product_id = 9; SELECT product_id, product_name FROM products;
ResultMySQL 8.4
mysql> DELETE FROM products
Query OK, 1 row affected
mysql> SELECT product_id, product_name FROM products
product_idproduct_name
1Wireless Mouse
2Mechanical Keyboard
3USB-C Hub
4Office Chair
5Standing Desk
6Notebook Pack
7Gel Pens
8Desk Lamp
8 rows in set
How this query works, step by step
  1. WHERE product_id = 9 matches exactly one row.
  2. MySQL removes it and reports 1 row affected.
  3. The SELECT shows the 8 remaining products.

Delete several rows

A WHERE that matches several rows deletes them all. Remove every line of order 1001:

SQL
DELETE FROM order_itemsWHERE order_id = 1001; SELECT * FROM order_items WHERE order_id IN (1001, 1002);
ResultMySQL 8.4
mysql> DELETE FROM order_items
Query OK, 2 rows affected
mysql> SELECT * FROM order_items WHERE order_id IN (1001, 1002)
order_idproduct_idquantity
100241
1 row in set
How this query works, step by step
  1. Order 1001 has two lines (products 2 and 6), so 2 rows affected.
  2. Order 1002’s line is untouched.

Check first with SELECT

Before deleting, run the same WHERE as a SELECT:

SQL
SELECT * FROM orders WHERE status = 'Cancelled';
ResultMySQL 8.4
order_idcustomer_idorder_datestatus
100432025-02-14Cancelled
1 row in set

If the result is what you expect, change SELECT * to DELETE and run it.

Foreign keys block orphan rows

Order 1004 still has an order line. The foreign key from order_items to orders stops you from deleting the order and leaving the line pointing at nothing:

MySQL
DELETE FROM orders WHERE order_id = 1004;
ResultMySQL 8.4
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`order_items`, CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`))

Delete the child rows first, then the parent:

SQL
DELETE FROM order_items WHERE order_id = 1004;DELETE FROM orders WHERE order_id = 1004;SELECT order_id, status FROM orders WHERE status = 'Cancelled';
ResultMySQL 8.4
mysql> DELETE FROM order_items WHERE order_id = 1004
Query OK, 1 row affected
mysql> DELETE FROM orders WHERE order_id = 1004
Query OK, 1 row affected
mysql> SELECT order_id, status FROM orders WHERE status = 'Cancelled'
Empty set
How this query works, step by step
  1. The first DELETE removes the order’s line (1 row).
  2. Now nothing references order 1004, so the second DELETE succeeds.
  3. No cancelled orders are left: Empty set.
Tip

A foreign key declared with ON DELETE CASCADE deletes the child rows automatically. It is convenient, but make sure that is really what you want.

Delete with a subquery

Delete customers who have never placed an order:

SQL
DELETE FROM customersWHERE customer_id NOT IN (SELECT customer_id FROM orders); SELECT customer_id, customer_name FROM customers;
ResultMySQL 8.4
mysql> DELETE FROM customers
Query OK, 2 rows affected
mysql> SELECT customer_id, customer_name FROM customers
customer_idcustomer_name
1Ananya Sharma
2Rohit Kumar
3Fatima Khan
4David Fernandes
5Ishita Bose
5 rows in set
How this query works, step by step
  1. The subquery lists every customer id that appears in orders.
  2. NOT IN matches customers 6 and 7, who have no orders. (orders.customer_id is NOT NULL, so the NOT IN NULL trap cannot happen here.)
  3. Two rows are deleted.

Delete with JOIN (MySQL)

Delete the order lines of every delivered order placed before February 2025:

MySQL
DELETE oiFROM order_items oiJOIN orders o ON o.order_id = oi.order_idWHERE o.status = 'Delivered' AND o.order_date < '2025-02-01'; SELECT COUNT(*) AS remaining_lines FROM order_items;
ResultMySQL 8.4
mysql> DELETE oi
Query OK, 3 rows affected
mysql> SELECT COUNT(*) AS remaining_lines FROM order_items
remaining_lines
13
1 row in set
How this query works, step by step
  1. DELETE oi names the table to delete from; orders is only used for the condition.
  2. Orders 1001 and 1002 match, with three lines between them.
  3. 16 lines minus 3 leaves 13.

Delete with ORDER BY and LIMIT

MySQL can delete a limited number of rows. This is useful for removing huge amounts of old data in small batches:

MySQL
DELETE FROM order_itemsORDER BY order_idLIMIT 2; SELECT COUNT(*) AS remaining_lines FROM order_items;
ResultMySQL 8.4
mysql> DELETE FROM order_items
Query OK, 2 rows affected
mysql> SELECT COUNT(*) AS remaining_lines FROM order_items
remaining_lines
14
1 row in set
How this query works, step by step
  1. ORDER BY order_id decides which rows go first: the two lines of order 1001.
  2. LIMIT 2 stops after two rows, leaving 14.
  3. In a real clean-up job you would use something like LIMIT 1000 and repeat until no rows are affected, so each batch holds locks only briefly.

TRUNCATE TABLE

TRUNCATE TABLE removes all rows at once and resets AUTO_INCREMENT counters:

MySQL
CREATE TABLE audit_log (id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(50));INSERT INTO audit_log (msg) VALUES ('login'), ('logout');TRUNCATE TABLE audit_log;INSERT INTO audit_log (msg) VALUES ('fresh start');SELECT * FROM audit_log;
ResultMySQL 8.4
mysql> CREATE TABLE audit_log (id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR…
Query OK, 0 rows affected
mysql> INSERT INTO audit_log (msg) VALUES ('login'), ('logout')
Query OK, 2 rows affected
mysql> TRUNCATE TABLE audit_log
Query OK, 0 rows affected
mysql> INSERT INTO audit_log (msg) VALUES ('fresh start')
Query OK, 1 row affected
mysql> SELECT * FROM audit_log
idmsg
1fresh start
1 row in set
How this query works, step by step
  1. Two rows are inserted with ids 1 and 2.
  2. TRUNCATE empties the table and resets the counter.
  3. The next insert gets id 1 again. After a DELETE FROM audit_log it would have got id 3.

DELETE vs TRUNCATE vs DROP

DELETETRUNCATEDROP TABLE
Removeschosen rows (WHERE)all rowsthe whole table
Table structurekeptkeptremoved
WHERE allowedyesnono
Speed on big tablesslow (row by row)fastfast
Resets AUTO_INCREMENTnoyesn/a
Fires triggersyesnono
Rollback in a transactionyesno (implicit commit)no (implicit commit)
Command typeDMLDDLDDL

Exercise: Remove out-of-stock products

Delete every product whose stock is 0. Run the matching SELECT first in your head, then show the remaining products.

Expected resultMySQL 8.4
mysql> DELETE FROM order_items WHERE product_id IN (SELECT product_id FROM pr…
Query OK, 1 row affected
mysql> DELETE FROM products WHERE stock = 0
Query OK, 1 row affected
mysql> SELECT product_id, product_name, stock FROM products
product_idproduct_namestock
1Wireless Mouse120
2Mechanical Keyboard45
4Office Chair12
5Standing Desk5
6Notebook Pack300
7Gel Pens250
8Desk Lamp30
9Monitor Arm18
8 rows in set
Hint 1

DELETE FROM products WHERE stock = 0;. The USB-C Hub has order lines, so delete those first.

Quick quiz

1. What does DELETE FROM customers; do?

2. Which command resets the AUTO_INCREMENT counter?

3. Why does deleting a parent row fail with error 1451?

4. Which can be rolled back inside a transaction?

DELETE FAQ

What is the difference between DELETE and TRUNCATE?

DELETE removes rows one by one, can use WHERE, fires triggers and can be rolled back. TRUNCATE removes all rows at once, resets AUTO_INCREMENT and cannot be rolled back in MySQL.

How do I delete duplicate rows in SQL?

Keep one row per duplicate group and delete the rest, for example with a self join on the duplicate columns and a.id > b.id, or with ROW_NUMBER() in a CTE. See Top N per Group.

Can I recover deleted rows?

Inside an uncommitted transaction, yes: ROLLBACK. After a commit, only from a backup or the binary log.

How do I delete rows from two tables at once in MySQL?

Name both tables after DELETE: DELETE o, oi FROM orders o JOIN order_items oi ON ... WHERE ...;

SQL UPDATE Statement

SQL Tutorial › Modifying Data › UPDATE

SQL UPDATE Statement

BeginnerLesson 19 of 6911 min read7 queries run on MySQL 8.4

UPDATE changes values in rows that already exist: give a raise, fix a typo, mark an order as shipped. It is simple to write and easy to get dangerously wrong, because without a WHERE clause it changes every row. This lesson shows the safe way to do it, step by step.

In this lesson
  • Update one or several columns with SET
  • Target the right rows with WHERE
  • Use calculations, CASE and joins in an UPDATE
  • Check your change before and after

UPDATE syntax

MySQL
UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition;

Sample data

employees · 12 rows (some columns)
emp_idfirst_namedept_idjob_titlesalarycity
100MeeraNULLCEO250000Mumbai
101Asha1Engineering Manager125000Bengaluru
102Rahul1Senior Developer98000Bengaluru
103Priya1QA Engineer72000Pune
104Karan1Developer72000Bengaluru
105Vikram2Sales Manager105000Mumbai
106Neha2Sales Executive54000Mumbai
107Arjun2Sales Executive48000Kolkata
108Kavya3Marketing Lead88000Pune
109Rohan3Content Writer45000Pune
110Sneha4HR Manager78000Delhi
111Amit1Intern25000Bengaluru

Update one row

Karan has been promoted:

SQL
UPDATE employeesSET job_title = 'Senior Developer'WHERE emp_id = 104; SELECT emp_id, first_name, job_title FROM employees WHERE emp_id = 104;
ResultMySQL 8.4
mysql> UPDATE employees
Query OK, 1 row affected
mysql> SELECT emp_id, first_name, job_title FROM employees WHERE emp_id = 104
emp_idfirst_namejob_title
104KaranSenior Developer
1 row in set
How this query works, step by step
  1. WHERE emp_id = 104 selects exactly one row, because emp_id is the primary key.
  2. SET job_title = 'Senior Developer' replaces the old value in that row.
  3. MySQL reports 1 row affected. The SELECT confirms the change.

Update several columns

Separate the assignments with commas:

SQL
UPDATE employeesSET city = 'Hyderabad', salary = 80000WHERE emp_id = 103; SELECT emp_id, first_name, city, salary FROM employees WHERE emp_id = 103;
ResultMySQL 8.4
mysql> UPDATE employees
Query OK, 1 row affected
mysql> SELECT emp_id, first_name, city, salary FROM employees WHERE emp_id = …
emp_idfirst_namecitysalary
103PriyaHyderabad80000
1 row in set

Calculated updates

The new value can use the old one. A 10% raise for the Marketing department:

MySQL
UPDATE employeesSET salary = salary * 1.10WHERE dept_id = 3; SELECT first_name, salary FROM employees WHERE dept_id = 3;
ResultMySQL 8.4
mysql> UPDATE employees
Query OK, 2 rows affected
mysql> SELECT first_name, salary FROM employees WHERE dept_id = 3
first_namesalary
Kavya96800
Rohan49500
2 rows in set
How this query works, step by step
  1. WHERE dept_id = 3 picks the two Marketing employees.
  2. For each, MySQL reads the current salary, multiplies it by 1.10 and stores the result.
  3. salary is an INT column, so MySQL rounds the result to a whole number: 88,000 becomes 96,800, and 45,000 becomes 49,500.

Update with CASE

Different raises for different job levels, in one statement:

MySQL
UPDATE employeesSET salary = CASE               WHEN salary < 50000 THEN salary + 5000               WHEN salary < 100000 THEN salary + 3000               ELSE salary             ENDWHERE dept_id = 2; SELECT first_name, salary FROM employees WHERE dept_id = 2;
ResultMySQL 8.4
mysql> UPDATE employees
Query OK, 2 rows affected
mysql> SELECT first_name, salary FROM employees WHERE dept_id = 2
first_namesalary
Vikram105000
Neha57000
Arjun53000
3 rows in set
How this query works, step by step
  1. WHERE dept_id = 2 picks the three Sales employees.
  2. The CASE works out each person’s new salary: Arjun (48,000) gets 5,000 more, Neha (54,000) gets 3,000 more.
  3. Vikram (105,000) falls into ELSE salary, so his value stays the same.
  4. MySQL reports 2 rows affected although 3 rows matched: it counts only rows whose value actually changed.

UPDATE with JOIN

Update one table using values from another. Move everyone in the Sales department to the department’s own location:

MySQL
UPDATE employees eJOIN departments d ON d.dept_id = e.dept_idSET e.city = d.locationWHERE d.dept_name = 'Sales'; SELECT e.first_name, e.city, d.locationFROM employees e JOIN departments d ON d.dept_id = e.dept_idWHERE d.dept_name = 'Sales';
ResultMySQL 8.4
mysql> UPDATE employees e
Query OK, 1 row affected
mysql> SELECT e.first_name, e.city, d.location
first_namecitylocation
VikramMumbaiMumbai
NehaMumbaiMumbai
ArjunMumbaiMumbai
3 rows in set
How this query works, step by step
  1. The join pairs each employee with their department row.
  2. WHERE d.dept_name = 'Sales' keeps the Sales employees.
  3. SET e.city = d.location copies Mumbai into each one’s city. Arjun moves from Kolkata; Vikram and Neha were already in Mumbai, so only 1 row changes.
Note

UPDATE ... JOIN is MySQL syntax. PostgreSQL uses UPDATE employees SET ... FROM departments WHERE ..., and SQL Server uses UPDATE e SET ... FROM employees e JOIN ....

Update with a subquery

The WHERE clause can use a subquery. Put every product that has never been ordered on sale, 300 off:

SQL
UPDATE productsSET price = price - 300WHERE product_id NOT IN (SELECT product_id FROM order_items); SELECT product_id, product_name, price FROM products WHERE product_id = 9;
ResultMySQL 8.4
mysql> UPDATE products
Query OK, 1 row affected
mysql> SELECT product_id, product_name, price FROM products WHERE product_id …
product_idproduct_nameprice
9Monitor Arm2499
1 row in set

Safe UPDATE habits

  1. Run the WHERE as a SELECT first. SELECT * FROM employees WHERE dept_id = 3; shows exactly which rows will change.
  2. Use a transaction for important changes, so you can ROLLBACK a mistake (Transactions).
  3. Turn on safe updates in interactive sessions: SET SQL_SAFE_UPDATES = 1; makes MySQL refuse UPDATE and DELETE without a key-based WHERE or LIMIT. MySQL Workbench enables it by default.
MySQL
SET SQL_SAFE_UPDATES = 1;UPDATE employees SET salary = 0;
ResultMySQL 8.4
mysql> SET SQL_SAFE_UPDATES = 1
Query OK, 0 rows affected
mysql> UPDATE employees SET salary = 0
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

Exercise: Restock

Set the stock of the USB-C Hub (product 3) to 75 and show the product.

Expected resultMySQL 8.4
mysql> UPDATE products
Query OK, 1 row affected
mysql> SELECT product_id, product_name, stock FROM products WHERE product_id …
product_idproduct_namestock
3USB-C Hub75
1 row in set
Hint 1

UPDATE products SET stock = 75 WHERE product_id = 3;

Exercise: Price rise

Raise the price of every Stationery product by 20 and show the Stationery products afterwards.

Expected resultMySQL 8.4
mysql> UPDATE products
Query OK, 2 rows affected
mysql> SELECT product_name, price FROM products WHERE category = 'Stationery'
product_nameprice
Notebook Pack269
Gel Pens219
2 rows in set
Hint 1

SET price = price + 20 with a WHERE on category.

Quick quiz

1. What does UPDATE products SET stock = 0; do?

2. How do you update two columns in one statement?

3. MySQL says “Rows matched: 3 Changed: 2”. Why only 2?

4. What is the best first step before a risky UPDATE?

UPDATE FAQ

How do I update multiple columns in SQL?

List them after SET, separated by commas: UPDATE t SET a = 1, b = 'x' WHERE id = 5;

How do I update a table using values from another table?

In MySQL, join the tables in the UPDATE: UPDATE t1 JOIN t2 ON ... SET t1.col = t2.col WHERE ...;

Can I undo an UPDATE?

Only if it ran inside a transaction that has not been committed: run ROLLBACK. With autocommit on (the default), the change is permanent, so restore from a backup.

What is safe update mode in MySQL?

SQL_SAFE_UPDATES = 1 blocks UPDATE and DELETE statements that have no WHERE on a key column and no LIMIT, protecting you from changing every row by accident.