China state Taiwan incorrect

[decodemyproject]

In the Chinese dataset there is a State incorrectly named Taiwan which holds cities like Haizhou and Jingjiang. The correct province or state for these cities under Taiwan is Jiangsu, as far as I can tell.

Example;

            {
                "id": 2255,
                "name": "Taiwan",
                "state_code": "TW",
                "latitude": "23.69781000",
                "longitude": "120.96051500",
                "type": "province",
                "cities": [
                    {
                        "id": 19592,
                        "name": "Haizhou",
                        "latitude": "34.58167000",
                        "longitude": "119.12889000"
                    },
                    {
                        "id": 19734,
                        "name": "Jingjiang",
                        "latitude": "32.01417000",
                        "longitude": "120.26250000"
                    },
                ]
            }

[dosubot[bot]]

To correct the state name from "Taiwan" to "Jiangsu" for cities like Haizhou and Jingjiang in the Chinese dataset, you can execute the following SQL update statement:

UPDATE public.states
SET name = 'Jiangsu'
WHERE name = 'Taiwan' AND id IN (
    SELECT id FROM public.states WHERE name IN ('Haizhou', 'Jingjiang')
);

This SQL statement updates the name field in the public.states table where the current state name is "Taiwan" and the state ID matches those of Haizhou and Jingjiang ^[1].

To continue talking to Dosu, mention @dosu.

[decodemyproject]

@dosu there is already a Jiangsu province/state in the dataset, it would make more sense to merge them wouldn't it?

[dosubot[bot]]

Yes, merging the states would make more sense. Here is a step-by-step approach to merge the state "Taiwan" into "Jiangsu" and update the associated cities:

1. Update the cities to reference the correct state ID for Jiangsu.
2. Delete the incorrect state "Taiwan".

First, find the ID of the correct state "Jiangsu":

SELECT id FROM public.states WHERE name = 'Jiangsu';

Assuming the ID of "Jiangsu" is 2250, update the cities currently associated with "Taiwan" to reference "Jiangsu":

UPDATE public.cities
SET state_id = 2250
WHERE state_id = (SELECT id FROM public.states WHERE name = 'Taiwan');

Finally, delete the incorrect state "Taiwan":

DELETE FROM public.states
WHERE name = 'Taiwan';

This will ensure that all cities are correctly associated with "Jiangsu" and the incorrect state "Taiwan" is removed from the dataset ^[1][2].

To continue talking to Dosu, mention @dosu.