[THE_KNOWN]

like i have 2 or more rows with the same name how do i delete the duplicate one?

[xxmitsu]

Sintax is:

Код:

delete from TABLE where FIELD ='VALUE'

Where 'VALUE' is the common value of your fields that you want to be deleted.

[THE_KNOWN]

but i want it to delete all entries which are repeating

[xxmitsu]

give me an example or export a portion of the database.

[THE_KNOWN]

Код:

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.netnl678059640cdad...2.87846735-- Host: localhost
-- Generation Time: Feb 12, 2011 at 09:07 AM
-- Server version: 5.5.8
-- PHP Version: 5.3.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `eeshrp`
--

-- --------------------------------------------------------

--
-- Table structure for table `ostats`
--

CREATE TABLE IF NOT EXISTS `ostats` (
  `name` varchar(24) NOT NULL,
  `org` int(2) NOT NULL,
  `req` int(2) NOT NULL,
  `lead` int(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ostats`
--

INSERT INTO `ostats` (`name`, `org`, `req`, `lead`) VALUES
('[vR]eesh', 1, 0, 1),
('hip', 0, 0, 0),
('AhraX', 5, 2, 1),
('FLaSH', 1, 0, 1),
('SofyanSchroder', 4, 0, 1),
('7777', 0, 0, 0),
('Arshavin', 0, 0, 0),
('TANO', 0, 0, 0),
('Bertalan_Roland', 0, 0, 0),
('[vR]Redneck', 3, 0, 1),
('Swamp.Rat', 3, 0, 2),
('FAILxClintx', 0, 1, 0),
('Gagan', 0, 1, 0),
('Yoel_Capo', 0, 0, 0),
('[vR]IraX', 0, 0, 0),
('r3xy_hunter', 0, 0, 0),
('mihei_snake', 0, 0, 0),
('Calin_Claudiu', 0, 1, 0),
('Daniel', 0, 0, 0),
('Mike_Mcfly', 0, 0, 0),
('Denis_Prichina', 0, 0, 0),
('Naber_', 0, 0, 0),
('iop[[', 0, 0, 0),
('killerkaox', 0, 0, 0),
('Calvin_Carter', 0, 0, 0),
('[uG]Lancer', 0, 0, 0),
('Sam_Lewis', 0, 0, 0),
('pap', 0, 0, 0),
('C_A', 0, 0, 0),
('matylais', 0, 0, 0),
('stw', 0, 0, 0),
('mattador', 0, 0, 0),
('Henry_Nash', 0, 1, 0),
('[ED]NoSs7[NGD]', 0, 0, 0),
('JellyBee', 0, 0, 0),
('Arvis_Stop', 0, 0, 0),
('[SkR]AhraX', 0, 0, 0),
('Nicco_Anderson', 0, 0, 0),
('Artix', 0, 0, 0),
('maciunio249', 0, 3, 0),
('lidia92', 4, 0, 2),
('Dmitrij', 0, 0, 0),
('Masterhulk', 0, 0, 0),
('[oz11]', 0, 0, 0),
('Venco_sk8', 0, 0, 0),
('[Bulzinho]Andrei', 0, 0, 0),
('[NND]', 0, 0, 0),
('peixe', 0, 0, 0),
('leon_pezic', 0, 0, 0),
('Nikola_Stevanovic', 0, 0, 0),
('isaac', 0, 0, 0),
('Sineas_Sauzar', 0, 0, 0),
('R4Y_Z3R', 0, 0, 0),
('tomo_legenda', 0, 0, 0),
('P1N0Y_CH4RL3S', 1, 0, 0),
('james', 0, 0, 0),
('[vR]Artix', 0, 0, 0),
('_Vallanzasca_', 0, 0, 0),
('Dev_Stoned', 0, 0, 0),
('Agnyte_Love_Raimux', 0, 0, 0),
('jusan', 0, 0, 0),
('ILVIR', 0, 0, 0),
('OZAN', 0, 0, 0),
('pityc55', 0, 0, 0),
('Todor', 0, 0, 0),
('Jetpack', 0, 0, 0),
('Claus', 0, 0, 0),
('Pollo', 0, 0, 0),
('Maciek12', 0, 0, 0),
('Ian', 0, 0, 0),
('zeniba', 0, 0, 0),
('Don_Luciano', 0, 0, 0),
('TomMan7531', 0, 0, 0),
('RJ3001', 0, 0, 0),
('[MG]II_FaTaL', 0, 0, 0),
('DoRu', 0, 0, 0),
('Guilherme_Roberts', 0, 0, 0),
('Miguel_Sanchez', 0, 0, 0),
('lol', 0, 5, 0),
('iFW_WiNd', 0, 0, 0),
('Wadim', 0, 0, 0),
('[SkR]Mostwanted', 5, 0, 0),
('vio900', 0, 0, 0),
('Rohail', 1, 0, 0),
('Redneck', 0, 0, 0),
('alex_a', 0, 0, 0),
('Ben_Ryan', 0, 0, 0),
('Faris', 1, 0, 0),
('SameT', 0, 0, 0),
('iFW_WiNdY', 0, 0, 0),
('grin58', 0, 0, 0),
('[LIGHT]MariokaS', 0, 0, 0),
('ShinZo', 0, 0, 0),
('Street_Racer', 5, 0, 0),
('Johnny_Franchetti', 0, 0, 0),
('[vR]IraX', 0, 0, 0),
('_maciek_', 0, 0, 0),
('$Pawlo$', 0, 0, 0),
('Nemanja_Looney', 0, 0, 0),
('guns', 0, 0, 0),
('Xerty', 0, 0, 0),
('Mitch_Rutledge', 0, 6, 0),
('danzel', 0, 0, 0),
('Hamtaro_Toxic', 0, 0, 0),
('Hai23', 0, 0, 0),
('l0l0l0l', 0, 0, 0),
('mhmdx5', 0, 0, 0),
('[Borneo_SA]_GLOCK', 0, 0, 0),
('Ruben_Llorca', 0, 0, 0),
('[RPG]laurika', 0, 0, 0),
('scouser', 0, 0, 0),
('Volvo', 0, 0, 0),
('bmx', 0, 0, 0),
('maciunio249', 0, 0, 0),
('_maciek_', 0, 0, 0),
('Swamp.Rat', 0, 0, 0),
('PhyscoWolf', 0, 1, 0),
('SkRLeeroyJenkins', 0, 0, 0),
('SELENA', 0, 0, 0),
('checkmate13', 0, 0, 0),
('lolek', 0, 0, 0),
('andge', 0, 0, 0),
('torres', 0, 0, 0),
('xXxForbiddenxXx', 0, 0, 0),
('Justin_Kistill', 0, 0, 0),
('Henry_Nash', 0, 1, 0),
('P1N0Y_CH4RL3S', 0, 0, 0),
('Edu_Smith', 0, 0, 0),
('Minicus', 0, 0, 0),
('[vR]test', 0, 0, 0),
('Ben_Ryan', 0, 0, 0),
('best_killa', 0, 0, 0),
('Swollen', 1, 0, 0);

here if you see [vR]Irax is repeating twice

[xxmitsu]

Ok, the:

Код:

delete from `ostats` where `name` ='[vR]Irax'

Код:

2 row(s) deleted. ( Query took 0.0086 sec )

[THE_KNOWN]

I meant I want to find all the entries which are repeatimg anddelete them keepimg one copy. I dont want to enter the syntax for each and every name

[xxmitsu]

You don't want to have duplicate names ? Then modify field types. Make them Unique or Index. That way when you try to insert the same value again, you will get an error and your database won't be redundant.

[THE_KNOWN]

oh thanks but should I make it unique or primary?

[xxmitsu]

That's a good question..
Primary it's used for indexing if you plan to select frequent amount of data based on that field match.
Judging by the purpose of the field you should use unique but you still have no primary field. I would go with primary.