skip to Main Content

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!

2

Answers


  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 = '' 
    
    Login or Signup to reply.
  2. You’re trying to compare values with DECIMAL type to a STRING, e.g. start_lat = '' which is not possible.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search