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!

Why don't you use a direct delete without cte? in any case why are you checking for empty string if the field is decimal? and by the way, all fields are nullable? — Leandro Bardelli, Mar 24 '23 at 01:03
This seems pretty over-complicated. But if your longitude and latitude columns are decimals, `start_lat = ''` is not going to work. — Andrew, Mar 24 '23 at 01:05
Hi guys. This is for a work portfolio. I am trying to show my coding skills so I can change careers into data analytics. That's why I thought creating a CTE and showing a more elegant procedure would be best. — Kenny Smith, Mar 24 '23 at 02:41
This table does not have any particular restrictions as far as primary or secondary keys. In my analysis, i will delete the ride_ids to prevent customer identification. — Kenny Smith, Mar 24 '23 at 02:43
@KennySmith not always the more elegant is the more pleasant, for coding sometimes is better go to the sure, quick, performant or even more, "out of the box" — Leandro Bardelli, Mar 24 '23 at 07:15
@LeandroBardelli I am experiencing this now. If I had done what you had suggested, it would have been much easier to identify the error. I will try to apply this advice for the rest of the project. Thank you! — Kenny Smith, Mar 24 '23 at 13:56
@KennySmith glad to help! if my question helps you, dont forget vote it up and/or mark it as correct! :) — Leandro Bardelli, Mar 24 '23 at 14:13

score 1 · Accepted Answer · answered Mar 24 '23 at 01:10

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 = ''

score 0 · Answer 2 · answered Mar 24 '23 at 01:12

0

You're trying to compare values with DECIMAL type to a STRING, e.g. start_lat = '' which is not possible.

answered Mar 24 '23 at 01:12

Marko

803
9
13

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

2 Answers2