Question 20696 in Mysql Question posted in
The official MySQL documentation can be found here.

Is it possible to alter specific data sets on a value changed to specific value? – Mysql

assuming I have a table similar to the following:

table [task]

Id status duedate solution datecompleted
1 inProgress 1/1/2024 ABC123 null
2 notStarted 1/2/2024 XYZ456 null
3 notStarted 1/2/2024 ABC123 null
4 Notstarted 1/2/2024 ABC123 null

Is there a way in MySQL to do the following:

Given I update the status value to completed on a task > dateCompleted is set to current DT.

Update duedate column for all rows with shared value from solutions column by extending other due dates by the amount of days past duedate that datecompleted is.

but only if the datecompleted is greater than the dueDate

Currently this process is being handled before we write to the data base. this is an attempt to alleviate some of the complexity of our system. I am relatively new to MySQL, if this is against best practices or not something within the functionality of SQL that would also be great to know.

Tags:

2

Answers


  1. 0

    The application logic is that there are two data changes.

    The first change is that a single row changes status (from "in progress" to "completed").

    The second event, that fires when the first one occurs, is that related rows should have some business logic applied.

    These events are related at the domain level, but at the database level represent two different events.

    There are 3 approaches, each with benefits and drawbacks.

    The first is to embed the logic in the application layer (as you do today).
    The application would presumably create a transaction, and then execute the SQL statements to update the initial task, and the related tasks.
    The benefits are that the business logic lives in the application, not the database – in general, you want business logic outside the database, because it tends to change more rapidly than database structure.

    The second is to use a stored procedure, which executes the SQL statements in a piece of business logic in a database artefact. Reasonable people can disagree about this – but stored procedures are harder to test than application code, and most people want business logic outside the database.

    The third option is to use a database trigger – whenever the "status" column changes, you can fire a trigger. Again, reasonable people can disagree – but triggers are even harder to test than stored procedures.

    Login or Signup to reply.
  2. 0

    I update the status value to completed on a task > dateCompleted is set to current DT.

    UPDATE task SET status = 'completed' 
WHERE dateCompleted <= CURDATE();

    Update duedate column for all rows with shared value from solutions column by extending other due dates by the amount of days past duedate that datecompleted is.

    I would recommend having another table to store the status of a solution, so you can change its status, due date, completed date in one place. This is related to the concept of relational database normalization — store each fact in only one place.

    But given the table you have, you could update all task rows this way:

    UPDATE task AS t1 JOIN task AS t2 
  ON t1.solution = t2.solution AND t1.duedate < t2.datecompleted
SET t1.duedate = t2.datecompleted;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search