I'm currently working on one of Danny Ma's SQL case studies with messy data that requires cleaning. One of the tables included a column (duration) with minutes as a varchar. I made a few mistakes while cleaning the column with the update and case statements. I did resolve the issue, but I don't want to go through that again with a table that has more than 100 rows. How can I prevent this from happening again? Is there an alternative way to change multiple values in a column?
I fixed the table so I had to add an image of the table from the website
I used the code below to remove minutes from certain values, but before that, I used the update statement to convert varchar nulls in regular nulls.
update [2. Pizza Runner].dbo.runner_orders
set duration = null
where duration = 'null'
update [2. Pizza Runner].dbo.runner_orders
set duration = case
when duration = '32 minutes' then '32'
when duration = '27 minutes' then '27'
when duration = '20 mins' then '20'
when duration = '10minutes' then '10'
when duration = '25mins' then '25'
when duration = '15 minute' then '15'
end
I don't have the results since I already fixed the column, but when I ran the code, rows 4 and 5 were turned into nulls.
After converting the duration column into an int datatype, I created a code that replaced the nulls with the correct values.
update [2. Pizza Runner].dbo.runner_orders
set duration = case
when order_id = 4 then 40
when order_id = 5 then 15
end
The same problem occurred but this time the values I previously changed were converted into nulls. In the end, I had to include every single value from the sample table to prevent any unwanted changes.
update [2. Pizza Runner].dbo.runner_orders
set duration = case
when order_id = 1 then 32
when order_id = 2 then 27
when order_id = 3 then 20
when order_id = 4 then 40
when order_id = 5 then 15
when order_id = 6 then null
when order_id = 7 then 25
when order_id = 8 then 15
when order_id = 9 then null
when order_id = 10 then 10
end