Page 1 of 1
[SOLVED] : INSERT INTO table IF NOT EXISTS problem
Posted: Mon Jan 25, 2010 12:57 am
by ecxzqute
Guys,
I need a little help with my query..
I need to check my database first if the data im going to insert already exists in a table.
here's my code:
Code: Select all
INSERT INTO tnames(name) VALUES ('a name')
SELECT name FROM tnames WHERE NOT EXISTS
(SELECT NAME FROM tnames WHERE name = 'a name');
my code produces a syntax error..
Can anyone help me with this?
I really need your help guys,
Ideas are greatly appreciated..
Re: help: INSERT INTO table IF NOT EXISTS problem
Posted: Mon Jan 25, 2010 1:25 am
by amargharat
If you are going to check with "name" then you should declare "name" as an unique key. so duplicate data in a "name" field will be ignored
Now, write the code to insert the data
e.g. insert into table_name(name) values('amar')
above query insert new entry if value "amar" not exists.
else entry will be ignored if exists.
so no need to check the condition
Re: help: INSERT INTO table IF NOT EXISTS problem
Posted: Mon Jan 25, 2010 1:42 am
by ecxzqute
my posted example is just a sample that i have created to test my query. the original consists of the following fields (id, ip, accNo, DateLog, TimeLog, WorkDate). Now, before i insert new record into that table i need to check whether the "to-be-inserted-data" already exists or not so i have to come up with a condition to check the table, if i set one of this fields to be the primary key then i may lost a bunch of data. like for example, i can have the same ip addresses, access nos, datelog and time log, also the work date,.
My Table is actually records of time keeping..
Thanx for your idea "mar" but i have actually tried that one already and my problem is that i lost lots of records..
regards,
ecxzqute
Re: help: INSERT INTO table IF NOT EXISTS problem
Posted: Mon Jan 25, 2010 1:58 am
by amargharat
if you want to check with multiple fields then just declare those fields as unique key
e.g. if you have table with fname, lname, dob
declare fname, lname and dob as unique keys then entry with similar fname, lname, dob wont be inserted
if "amar", "gharat", "29-10-1985" already exists then entry will be ignored
if "amar", "gharat", "29-11-1985" then entry will be insered bcos dob is diferrent.
basically you have declared unique key on those which fields you want to compare
Re: help: INSERT INTO table IF NOT EXISTS problem
Posted: Mon Jan 25, 2010 6:37 am
by ecxzqute
i made it,
Code: Select all
"INSERT INTO temployeeattendancelog
( AccessNo
, AccessNoPerMall
, DateLog
, TimeLog
, IPAddress
, ID_LogFileFormat
, ID_AttendanceLogType
)
SELECT 'SCLA-128'
,'128'
, '2009-06-01'
, '01:20:00'
, '192.168.0.100'
, '7'
, '1'
FROM DUAL
WHERE (
SELECT COUNT(*)
FROM tEmployeeAttendanceLog
WHERE
AccessNo = 'SCLA-128' and
AccessNoPermall = '128' and
DateLog = '2009-06-01' and
TimeLog = '01:20:00' and
IPAddress = '192.168.0.100' and
ID_LogFileFormat = '7' and
ID_AttendanceLogType = '1'
) = 0
thanx to Ales
http://articles.itecsoftware.com/web-de ... s-in-mysql