How to create an MySQL conditional function that iterates through a table and takes multiple parameters

Question

I am very new to PL/SQL, but I would like to populate an empty field for all entries in a table, based on the values of several fields in a giving row. So the logic I would like to put in place is, but there are several thing in which I need to keep in mind when setting up a table for a Car Dealership...

First, group by the distinct used_car_ids, then update the column based on.....

1. If the used_car_id column is equal to the new_car_id, and both the drive_mode and saftey_rating is 1, then set the Parking_Spot number to 1 as highest precedence.

2. If the used_car_id column is equal to the new_car_id, but the drive_mode is 1 and saftey_rating is 2, then set the Parking_Spot column to 2 or the available consecutive Parking_Spot number

   • If there are more conditions similar to this, but only the saftey_rating is different then, I would like to keep increasing the Parking_Spot number to the next available consecutive Parking_spot number for that entry

3. If the used_car_id column is equal to the new_car_id, but the drive_mode is 2 and saftey_rating is 1, then set the Parking_Spot column to 3, or the next available Parking_Spot number

4. If the used_car_id column is equal to the new_car_id, but the drive_mode is 2 and saftey_rating is 2, then set the Parking_Spot number to 4, or the next available Parking_Spot number
   • Same rules apply, as above.

If the highest precedence case isn't met then it should treat the 2 use case as the highest and put that as Parking_Spot=1.

This is a small picture, of what I would ideally want. So as you can see in the picture the Parking Spot number is 1, and 2 depending if the used_car_id, new_car_id, drive_mode, and safety_rating are 1 and 2, but once the used_car_id and new_car_id is different the Parking Spot number increases to 3 and 4.

I'm assuming I need a function to input 4 parameters and based on those fields then I return a number as the Parking Spot. But I am still a bit unsure of how to go about it. Any help would be appreciated. Thanks.

Answer 1

It's not clear from your tags whether you're actually using the Oracle database or MySQL. This answer assumes the former.

Your question has a couple gaps, but it seems like you want to give each row a sequential number based on the drive_mode and safety_rating. You mention "used_car_id column is equal to the new_car_id" several times, but never mention what you want to do when they are not equal.

For the question as written, it seems that this can be solved in a fairly straightforward way using analytic functions. In this case, we'll simply tell the function to order the rows by drive_mode_id and safety_rating and give each row from the resulting order a consecutive number:

SELECT id,
       used_car_id,
       new_car_id,
       car_features_id,
       drive_mode_id,
       safety_rating,
       ROW_NUMBER () OVER (ORDER BY drive_mode, safety_rating)
           AS parking_spot
FROM   your_table
WHERE  used_car_id = new_car_id

---

It looks like MySQL supports analytic functions (aka window functions) as of 8.0.2, so the above solution should still work if you are using that version or later (if not, this answer might help).

To update that to also consider used_car_id = new_car_id as a sort condition, you'll need to add a conditional to the ORDER BY:

SELECT id,
       used_car_id,
       new_car_id,
       car_features_id,
       drive_mode_id,
       safety_rating,
       ROW_NUMBER ()
       OVER (
           ORDER BY
               CASE WHEN used_car_id = new_car_id THEN 1 ELSE 2 END,
               drive_mode,
               safety_rating)
           AS parking_spot
FROM   your_table

This is untested, so I can't guarantee that this is entirely syntactically correct, but it should be close.