-
Step 1
Use the INSERT command to input data. To perform an update MySQL query, it's imperative to have a row in the database to change, preferably with a unique ID referring to that row.
-
Step 2
Make sure that every ID in the table is unique by using an INTEGER AUTO_INCREMENT column. UPDATE commands can update the wrong row if a unique ID isn't used. When using an ID field, that field isn't included in the INSERT command, as the AUTO_INCREMENT attribute takes care of that field for you. Example:
INSERT INTO customers name,ph_number,balance VALUES ("John Doe","555-5555",0.0); -
Step 3
Find the data again using a SELECT query. Here, to update the balance, you first have to pull the old balance out of the table. Since all you are interested in is the balance and the ID number, those are the only two columns you need to pull out. Later, the application code using these queries will add to or subtract from the balance. Example:
SELECT id,balance FROM customers WHERE name='John Doe' AND ph_number="555-5555"; -
Step 4
Update the data. Perform the UPDATE MySQL Query using the UPDATE command. Now that you have the ID and the updated data, you have all the information you need to proceed. UPDATE looks like a combination between INSERT and SELECT. This example supposes the balance and ID are something you got from the previous query. Example:
UPDATE customers SET balance=12.34 WHERE id=22; -
Step 5
Avoid making mistakes in the WHERE clause, since you can inadvertently update more than one row. For example, if you used this query instead of the previous one, it would accidentally update the balance of every customer named John Doe. Example:
UPDATE customers SET balance=12.34 WHERE name="John Doe";
