SQL for Inventory Management: Adding and Removing Stock with a Single Query

This method uses the UPDATE statement along with an arithmetic operator to modify the existing value in the column. Here's the syntax:

UPDATE table_name
SET column_name = column_name [ + 1 | - 1 ]
WHERE condition;

• table_name: The name of the table you want to update.
• column_name: The name of the integer column you want to modify.
• + 1: This increases the current value in the column by 1.
• WHERE condition: This is optional and allows you to specify which rows in the table should be updated based on a certain criteria. If omitted, all rows will be affected.

For example, if you have a table named users with a column named login_count, the following query would increment the login_count by 1 for the user with ID 1:

UPDATE users
SET login_count = login_count + 1
WHERE id = 1;

INSERT ... ON DUPLICATE KEY UPDATE:

This approach is useful when you're unsure whether a specific row already exists in the table. It combines an INSERT statement with an ON DUPLICATE KEY UPDATE clause. Here's the breakdown:

• The INSERT statement attempts to insert a new row with specific values.
• The ON DUPLICATE KEY UPDATE clause comes into play if a row with the same unique key (usually an ID) already exists. It then updates the specified column (in this case, increasing or decreasing by 1) instead of inserting a duplicate row.

Example:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column_name = column_name [ + 1 | - 1 ];

This approach can be less efficient than the first method, so it's generally recommended for scenarios where checking for existing rows is necessary.

Choosing the right approach:

• If you know exactly which rows to update and want a simpler syntax, use the UPDATE with the arithmetic operator.
• If you're unsure about existing rows and want to handle potential duplicates, use INSERT ... ON DUPLICATE KEY UPDATE.

This example updates the stock column in the inventory table by 2 for an item with id 10:

UPDATE inventory
SET stock = stock + 2
WHERE id = 10;

This example subtracts 1 from the points column in the users table for a user with username "Alice":

UPDATE users
SET points = points - 1
WHERE username = 'Alice';

Example 3: Updating on duplicate key (Increase)

This example attempts to insert a new row into the scores table for a player named "Bob" with a score of 80. If a row for "Bob" already exists, it updates the existing score by adding 1:

INSERT INTO scores (player_name, score)
VALUES ('Bob', 80)
ON DUPLICATE KEY UPDATE score = score + 1;

This example tries to insert a row for a product with id 5 and quantity 0. If the product already exists, it subtracts 1 from the current quantity:

INSERT INTO products (id, quantity)
VALUES (5, 0)
ON DUPLICATE KEY UPDATE quantity = quantity - 1;

1. Stored Procedures:

You can create a stored procedure that encapsulates the logic for updating the value. This procedure could take arguments like the table name, column name, and increment/decrement flag. Inside the procedure, you could use UPDATE with arithmetic operators based on the flag. This approach offers modularity and reusability, especially if the update logic becomes more complex in the future.

1. CASE statement with UPDATE:

This method leverages the CASE statement within the UPDATE command. Here's the syntax:

UPDATE table_name
SET column_name = 
  CASE WHEN condition THEN column_name + 1
       ELSE column_name - 1
  END
WHERE condition2;

• condition: This specifies when to increment (e.g., a specific value in another column).
• condition2: This is the optional WHERE clause to filter specific rows for update.

This approach allows for conditional updates based on different criteria within a single statement.

UPDATE orders
SET quantity = 
  CASE WHEN shipped = 1 THEN quantity + 1
       ELSE quantity - 1
  END
WHERE order_id = 10;

This example increases the quantity by 1 for order ID 10 if it's shipped, otherwise it decreases by 1.

• If you need reusable logic for updates with variations, stored procedures offer a good option.
• If you have a simple conditional update within a single statement, the CASE statement with UPDATE can be suitable.