Using a CTE to delete Nulls Shows Error [1292] [22001]: Data Truncation: Truncated Incorrect DECIMAL

Question

I am trying to find a way to delete all rows with NULL values in a table. On this post, I saw that you can't specify a target table from updates in the FROM clause. Therefore, I tried building a CTE to perform a bulk delete of all rows with NULL values in a table. This is the table structure: Table Structure

I created the following code:

WITH cte_delete AS
(
    SELECT ride_id
    FROM bikes.work
    WHERE ride_id IS NULL OR ride_id = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE bike_type IS NULL OR bike_type = ''
        UNION ALL    
    SELECT ride_id
    FROM bikes.work
    WHERE started_at IS NULL OR started_at = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE ended_at IS NULL OR ended_at = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE start_sta_name IS NULL OR start_sta_name = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE start_sta_id IS NULL OR start_sta_id = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE end_sta_name IS NULL OR end_sta_name = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE end_sta_id IS NULL OR end_sta_id = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE start_lat IS NULL OR start_lat = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE start_lng IS NULL OR start_lng = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE end_lat IS NULL OR end_lat = ''
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE end_lng IS NULL OR end_lng = ''   
        UNION ALL
    SELECT ride_id
    FROM bikes.work
    WHERE user_type IS NULL OR user_type = '' 
)
DELETE FROM bikes.WORK 
WHERE ride_id = (SELECT ride_id FROM cte_delete);

This gave me the error:

SQL Error [1292] [22001]: Data truncation: Truncated incorrect DECIMAL value: ''.

I thought my query just pulled the ride_ids, so I don't know why the latitude and longitude values are acting up. I used DECIMAL(12,10) because I read that it is best for migrating data. Does anyone have any tips for completing the bulk delete of any rows with NULL values? Thank you!

Answer 1

Don't check for empty string if the field is not VARCHAR. Also only check for NULL if the field is nullable.

DELETE FROM bikes.WORK 
     WHERE ride_id IS NULL OR ride_id = ''
        OR bike_type IS NULL OR bike_type = ''
        OR started_at IS NULL OR started_at = ''
        OR ended_at IS NULL OR ended_at = ''
        OR start_sta_name IS NULL OR start_sta_name = ''
        OR start_sta_id IS NULL OR start_sta_id = ''
        OR end_sta_name IS NULL OR end_sta_name = ''
        OR end_sta_id IS NULL OR end_sta_id = ''
        OR start_lat IS NULL 
        OR start_lng IS NULL
        OR end_lat IS NULL 
        OR end_lng IS NULL 
        OR user_type IS NULL OR user_type = ''