テーブルの中身の重複したデータがある場合、一つを残して他を削除したい。

Question

MySQLバージョン 5.0.95
NAMEテーブル、カラム（id,name）に於いて
1,田中 太郎
2,田中　太郎
3,田中　　　太郎
4,佐藤 太郎
5,佐藤　　　太郎
6佐藤　太郎
等あらゆる空白が入った重複するデータが入ったテーブルがあります(どのように空白が入っているかは解らない。半角なのか全角なのか、それらの空白がどれだけ連続するのかも解らない)。
NAMEテーブルの中身を、重複した、空白を無視した状態の文字列を一つ残したまま削除した状態にしたいです。
つまり
変更後
田中　太郎
佐藤　太郎
ようなテーブルに更新（変更、作り直し）したいです。

ただし、一つ残すそのデータにもともと含まれていた空白はそのままにしたいです。田中 太郎でも田中　　　太郎でも、どれでも良いから一つ空白が入っていたままの状態で残したいです。

考えたことは以下です。
①nameテーブルから全ての空白をreplaceにより詰めたデータを、一時的なテーブルの中に追加する。（newnameというテーブル名）
②newnameの中で重複するデータを見つけてidの若い方を残し、それ以外を削除する。
③nameテーブルに於いてnewnameにあるidと同じidを見つけたら削除する。 という形式でコードを書けば、空白は元のままで重複する余分なデータを削除できるのではないかと考えたのですが、無理はないでしょうか。無理がなければ、 このようなコードはどのようにしたら書けるのでしょうか。

試したコードは

create temporary table newname(
idx int,
name varchar(255)
);

INSERT into newname(idx,name)
select 
 id
 ,name
 ,case
 when instr(name,' ')>0
 then replace(replace(name,'　',''),' ','')

 when instr(name,'　')>0
 then replace(replace(name,'　',''),' ','')
 end as newname
 from test

この段階で1136番のエラが返ってきます。
カラムの数があっていないとあります。idx,nameとid,nameの指定なのでカラムの数は合っていると思うのですが、どこを見落としているのでしょうか。

Comments

[yambejp]

mysql5.0系は使うべきではありません

[退会済みユーザー]

セキュリティの問題でしょうか？
試しに少し使ってみてサービスに幾らかの反響反応があったら、最新のバージョンで、という考えは甘いでしょうか。

[yambejp]

https://www.softagency.co.jp/products/mysql/support

まず、サポートがきれていること。最低でも5.7まで上げておかないとなにかあっても自己責任です。
また、かりにスキルでなんとかするとしても機能が少なすぎてちょっと複雑なことをやろうとしてもすぐ手詰まりになります

Answer 1

とりあえずこんな感じ

create table tbl(id int primary key,name varchar(30));
insert into tbl values
(1,'田中 太郎'),
(2,'田中　太郎'),
(3,'田中　　　太郎'),
(4,'佐藤 太郎'),
(5,'佐藤　　　太郎'),
(6,'佐藤　太郎');


select *,
case
when instr(name,' ')>0
then concat(substring_index(name,' ',1) ,' ',substring_index(name,' ',-1) )
when instr(name,'　')>0
then concat(substring_index(name,'　',1) ,' ',substring_index(name,'　',-1) )
end as newname
from tbl

上記を利用して更新して削除

update tbl set name=case
when instr(name,' ')>0
then concat(substring_index(name,' ',1) ,' ',substring_index(name,' ',-1) )
when instr(name,'　')>0
then concat(substring_index(name,'　',1) ,' ',substring_index(name,'　',-1) )
end;

delete from tbl where id in(
select id from tbl as t1
where exists(select 1 from tbl where name=t1.name and id<t1.id)
);

Comments

[退会済みユーザー]

ご回答ありがとうございます。
教えていただいたコードを試してみました。上の例で挙げた名前のテーブルでは問題ないのですが、全角半角が入り混じっているデータやミドルネームがある場合に対応できないのと、後半のコードは1093?番のエラが返ってきます。

そこであれこれ考えてみたのですが、
①nameテーブルから全ての空白をreplaceにより詰めたデータを、一時的なテーブルの中に追加する。（newnameというテーブル名）
②newnameの中で重複するデータを見つけてidの若い方を残し、それ以外を削除する。
③nameテーブルに於いてnewnameにあるidと同じidを見つけたら削除する。
という形式でコードを書けば、空白は元のままで重複する余分なデータを削除できるのではないかと考えたのですが、無理はないでしょうか。無理がなければ、
このようなコードはどのようにしたら書けるのでしょうか。
というようにコードを丸々教えてくれという質問はNGかと思うのですが、もちろん自分でも検索してみたのですが、同じ局面に至った人がいないのか、あるいは検索の仕方が悪いのか、中々見つかりません。引き続き自分でも調べているので、お力添え願いたいのです。

[退会済みユーザー]

試したコードです。
create temporary table newname(
idx int,
name varchar(255)
);

INSERT into newname(idx,name)
select *
id
,name
,case
when instr(name,' ')>0
then replace(replace(name,'　',''),' ','')

when instr(name,'　')>0
then replace(replace(name,'　',''),' ','')
end as newname
from test

が1136のエラが返ってきました。

Answer 2

多分正規表現で半角スペースがエスケープ処理されていないからですね。