This MySQL Update lesson is for a beginner. Read the short notes, run each example, then change one name or number and run it again.
UPDATE changes columns on the rows that match WHERE. Without WHERE, every row is changed.
Set only the columns you mean to change.
Example 1: Start here
UPDATE learner SET city = %s WHERE id = %s is the safe form.
rows = [{"id": 1, "city": "Chennai"}] # a list; the values stay in this order
for row in rows: # repeat the indented lines once per value
if row["id"] == 1: # run the indented lines only when this is true
row["city"] = "Madurai" # change this one field
print(rows)
Example 2: Filter the rows
MySQL Update is easier to see as a list of records. The filter keeps every row whose seats value is at least 10.
rows = [{"topic": "MySQL Update", "seats": 21}, {"topic": "SQL", "seats": 12}] # a list; the values stay in this order
print([row["topic"] for row in rows if row["seats"] >= 10]) # keep the items that pass the test
Example 3: Update one field
An update changes one field and leaves the rest of the record alone. On a database you would run an UPDATE or an update_one for the same change.
row = {"topic": "MySQL Update", "seats": 21} # a dictionary; each key has one value
row["seats"] = row["seats"] + 1 # change this one field
print(row)
Example 4: Count the matches
Counting matches is the idea behind COUNT(*). Here the number 21 appears twice.
rows = [21, 4, 21] # a list; the values stay in this order
print(sum(1 for value in rows if value == 21)) # keep the items that pass the test
UPDATE learner SET city = %s WHERE id = %s is the safe form.
