duplicate data not insert in mysql

Try this , Seems like NULL issue

AND projects IS NOT NULL

INSERT INTO  'projects' ('id' ,'projects', 'created' ) VALUES ( '', 'PHP', '' ) 
WHERE NOT EXISTS (SELECT *
                  FROM projects
                  WHERE projects = 'PHP' AND projects IS NOT NULL)

Select allOpen in new window

Are you just wanting to insert one row when it doesn't exist?

yes. if data exit its skip insert or data not exit its insert.

Please try this !!

INSERT INTO  'projects' ('id' ,'projects', 'created' ) 
SELECT '', 'PHP', '' FROM Dual
WHERE NOT EXISTS (SELECT *
                  FROM projects
                  WHERE projects = 'PHP')

Select allOpen in new window

sql show error when i run query

Please try the last one..

same error..

Actually on my machine it looks fine. May be try below....

INSERT INTO projects (id ,projects, created)
SELECT '', 'PHP', '' FROM Dual
WHERE NOT EXISTS (SELECT *
                  FROM projects
                  WHERE projects = 'PHP')

i m facing same problem.

What do you need to do?
Are you trying to insert a row with  just the value 'PHP' in field projects as many times as  it already exists?

in a table 4k records when i run my sql query if data exit in table its skip insert and if data not exit in table its insert.

i want skip duplicate entry.

Make projects a unique key.
Then

INSERT INTO  `projects` (`id` ,`projects`, `created` ) VALUES ( '', 'PHP', '' )
   ON DUPLICATE KEY UPDATE projects = projects;

Select allOpen in new window

Try this..

INSERT IGNORE INTO projects (id, projects,created) VALUES ( '', 'PHP', '' )

But wich data? You're inserting a row with just 'PHP' in field project. When must this be done? When another record with 'PHP' is not found? And what about indexes (is that ID field a Key)?

Looking at your query I am going to assume that id is an AUTONUMBER and created is a time stamp in  which case you probably want this

INSERT INTO  `projects` (`projects` ) VALUES ('PHP' )
   ON DUPLICATE KEY UPDATE projects = projects;

Select allOpen in new window

LIMIT 1 would probably work too and be more efficient than using "distinct"...

Hi julian,
when i run query i get error

my table structure

	CREATE TABLE `projects` (
  `id` int(11) NOT NULL,
  `projects` varchar(101) NOT NULL,
  `projects_info` varchar(255) NOT NULL,
  `created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Select allOpen in new window

It would probably be sensible to give us a copy and paste of the structure of the projects table so we can see whether what you're asking is possible, or whether we need to change some values before it will work.

Did you try my solution?

This may be because of the other columns .. Every time when you insert it must be inserting some date in below column.

 `created` datetime NOT NULL

and because of that it is not considering the row as duplicate one. It must be a inserting todays date.

Hi Terry,

when i get your query i get error.

Can you please run the query:

SELECT VERSION();

Select allOpen in new window

so we know what version of MySQL you're running?

Also, which software are you testing the query in? phpmyadmin?

Please check if you able to insert below data. Run one query at a time.


INSERT IGNORE INTO projects (id, projects,projects_info,created) VALUES ( '', 'PHP', '' , '2016/01/01')

INSERT IGNORE INTO projects (id, projects,projects_info,created) VALUES ( '', 'PHP', '' , '2016/01/01')

VERSION is 10.1.13-MariaDB and i am using xampp phpmyadmin

Can you post screen grabs WITHOUT the error block blocking what your query looks like.

Something is not right - you are getting errors you shouldn't be getting - most of the solutions posted here are valid and should not generate errors - if you are getting errors then something else is not right.

That is weird... the query I provided was tested successfully in MySQL 10.0.27-MariaDB in PhpMyAdmin. I wonder if there's end of line characters doing something odd when it's pasted in?

CREATE TABLE `projects` (
  `id` int(11) NOT NULL,
  `projects` varchar(101) NOT NULL,
  `projects_info` varchar(255) NOT NULL,
  `created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `projects`
--
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `no_duplicate` (`projects`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `projects`
--
ALTER TABLE `projects`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Select allOpen in new window

Note though that you can't insert a blank value into the `id` column of the table unless you give the column AUTO_INCREMENT

Similarly, for the `created` column, it will need DEFAULT CURRENT_TIMESTAMP if you want to insert a blank value.

You may need to use NULL instead of '' for both columns

I see `id` is ok then, but you'll still need to do something for `created`

id is auto increment

I would also make createdate a timestamp field with a default value CURRENT_TIMESTAMP

alter table .`projects` 
   change `created` `created` timestamp default CURRENT_TIMESTAMP NOT NULL

Select allOpen in new window

I said that there could be something related indexes...
As Terry is right suggesting you cannot insert simply a value per row without add also the required field values...
You're in good hands anyway with Terry and other participants experts

Hi Julian,
when i run your  

ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`),
  ADD UNIQUE KEY `no_duplicate` (`projects`);

Select allOpen in new window

this query error show
#1068 - Multiple primary key defined

You still need to alter the table for the `created` column as per Julian's comment.

i have alter the table for the `created` column its done but when i alter projects for UNIQUE KEY its show error.

#1062 - Duplicate entry 'AutoCAD' for key 'projects'

Yes since you have duplicate entry in the table the engine is not allowing you to create the unique constraint.

Can you make it AutoCAD to AutoCAD1 for time being.

Do you want existing duplicate values to remain in the `projects` column? If so, then you don't want to be updating the existing data.

My suggested query will still work when there are existing duplicates, but the other suggestions that use INSERT IGNORE will not work in that case.

If  you are happy to remove or update records where the `projects` column is non-unique, then you'll be able to add a unique key on that column and INSERT IGNORE will work for what you're trying to do.

Its work.

Thank you for giving your valuable time to me and solving my problem.

Thanx to all .

So finally what you did. Unique projects column or Non unique.

Also could you please mark one or more answer as accepted solution and close the question.

Thanks !

unique projects

Thanx