Standardizing color names

In the previous exercise, the DIFFERENCE() function was used to identify colors that closely matched our desired representation of the color GRAY. However, this approach retained a number of records where the vehicle_color value may or may not be gray. Specifically, the string GR (green) has the same Soundex code as the string GRAY. Fortunately, records with these vehicle_color values can be excluded from the set of records that should be changed.

In this exercise, you will assign a consistent gray vehicle_color value by identifying similar strings that represent the same color. Again, the fuzzystrmatch module has already been installed for you.

この演習はコースの一部です

Cleaning Data in PostgreSQL Databases

コースを見る

演習の手順

Complete the SET clause to assign 'GRAY' as the vehicle_color for records with a vehicle_color value having a matching Soundex code to the Soundex code for 'GRAY'.
Update the WHERE clause of the subquery so that the summons_number values returned exclude summons_number values from records with 'GR' as the vehicle_color value.

実践的なインタラクティブ演習

このサンプルコードを完成させて、この演習に挑戦してみましょう。

UPDATE 
	parking_violation
SET 
	-- Update vehicle_color to `GRAY`
	___ = ___
WHERE 
	summons_number IN (
      SELECT
        summons_number
      FROM
        parking_violation
      WHERE
        DIFFERENCE(vehicle_color, 'GRAY') = 4 AND
        -- Filter out records that have GR as vehicle_color
        ___ != ___
);

コードを編集して実行