CODEWITHSUNDEEP
mysql
Hannes Jordaan
Hannes Jordaan

Reputation: 13

Trying to remove duplicate entries in MySQL database with a twist

First off, I'm a nooob. I've got a Database Table with lots of duplicate rows. I created a new clean database table (ufx_multicity) and tried the following command in MySQL to copy all the rows from the old database table (ufx_multicity2) and to ignore the duplicate entries.

INSERT INTO ufx_multicity 
            (`country_id`, 
             `zones_id`, 
             `cityname`, 
             `city_slug`, 
             `lat`, 
             `lng`, 
             `scall_factor`, 
             `is_zoom_home`, 
             `map_type`, 
             `post_type`, 
             `categories`, 
             `is_default`, 
             `message`, 
             `color`, 
             `images`, 
             `header_color`, 
             `header_image`, 
             `cat_scall_factor`, 
             `is_zoom_cat`) 
SELECT DISTINCT `cityname`, 
                `city_slug`, 
                `lat`, 
                `lng` 
FROM   ufx_multicity2;

I get the following error:

1136 - Column count doesn't match value count at row 1

How do I fix this? Your help is appreciated.

Upvotes: 1

Views: 47

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562230

Column count doesn't match value count

Isn't this error message pretty descriptive?

Your INSERT is naming 19 columns that it expects you to provide data for.

But the SELECT only names four columns. How is the INSERT supposed to know which four of the nineteen columns to put data into? And what values should it use for the other fifteen columns?

The number of columns must be the same, and in the same order. Either name fewer columns in the INSERT, or else provide more columns in the SELECT.

Re your question about duplicates:

You might misunderstand how SELECT DISTINCT works. It means that a row counts as distinct if any column in the row is different from the columns in another row. And DISTINCT always applies includes all of the columns of the select-list in this determination.

You could try using GROUP BY instead of DISTINCT.

INSERT INTO wfx_multicity (country_id, zones_id, cityname, 
  city_slug, lat, lng, 
  scall_factor, is_zoom_home, map_type, post_type, categories, 
  is_default, message, color, images, header_color) 
SELECT MAX(country_id), MAX(zones_id), cityname, city_slug, lat, lng, 
  MAX(scall_factor), MAX(is_zoom_home), MAX(map_type), MAX(post_type), MAX(categories),
  MAX(is_default), MAX(message), MAX(color), MAX(images), MAX(header_color) 
FROM wfx_multicity2
GROUP BY cityname, city_slug, lat, lng;

GROUP BY works differently. It reduces the result of the SELECT to one row for each distinct combination of the columns named in the GROUP BY. This gives you the chance to choose a subset of columns for reducing the rows to those with distinct values over just those few columns.

Notice I put the grouping function MAX() around each of the other columns. If you don't do this, MySQL chooses values for those columns arbitrarily from rows in each group.

Consider this table:

col1 col2 col3
---- ---- ----
1    14    'X'
1    12    'Z'
1    14    'Y'
2    27    'K'
2    9     'K'
3    6     'Q'

If I query from this table like so:

SELECT col1, col2, col3 FROM mytable GROUP BY col1;

It should return only three rows, because there are three distinct values in col1. But what should it return in the other columns? It's ambiguous. In practice, MySQL might return the values from the first row in each group, but it makes no guarantee of doing this consistently.

In strict SQL, the ambiguous form of the query is an error (and for what it's worth, MySQL 5.7 will make it an error too).

SELECT col1, MAX(col2), MAX(col3) FROM mytable GROUP BY col1;

Using a grouping function like MAX() resolves the ambiguity, but does this return all the values from the last row in each group? Not really -- it returns the largest value in each column, even if those values come from different rows.

col1 MAX(col2) MAX(col3)
---- --------- ---------
1    14        'Z' <-- none of the rows has this combination
2    27        'K'
3    6         'Q'

So the next question you need to ask is, which row do I want to pick rows from? Or does it matter?

Upvotes: 0

markt
markt

Reputation: 903

You are trying to insert into 19 columns:

INSERT INTO ufx_multicity (country_id, zones_id, cityname, city_slug, lat, lng, scall_factor, is_zoom_home, map_type, post_type, categories, is_default, message, color, images, header_color, header_image, cat_scall_factor, is_zoom_cat)

data from only 4 columns

SELECT DISTINCT cityname, city_slug, lat, lng FROM ufx_multicity2;

The two must match so you have 4 columns inserted into 4 columns:

 INSERT INTO ufx_multicity (cityname, city_slug, lat, lng) SELECT DISTINCT cityname, city_slug, lat, lng FROM ufx_multicity2;

Upvotes: 1

Related Questions