where not exists problem!

[richiemac]

Hi all, i'm trying to build a SQL statement, commStr = "insert into mytable values(default,?number,?text,?date) " + "select * from mytable " + "where not exists(select * from mytable where int_field = '" + System.Convert.ToInt32(TextBox1.Text) + "')"; and I keep getting the following error System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> MySql.Data.MySqlClient.MySqlException: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from mytable where not exists(select * from mytable where int_field = '' at line 1 at MySql.Data.MySqlClient.PacketReader.CheckForError() at MySql.Data.MySqlClient.PacketReader.ReadHeader() at MySql.Data.MySqlClient.PacketReader.OpenPacket() at MySql.Data.MySqlClient.NativeDriver.Prepare(String sql, String[] parmNames) at MySql.Data.MySqlClient.MySqlCommand.Prepare() at Service.InsertDB(String insStr, String connStr, Int32 inInt, String inText, String inDate) the SQL string that is being passed looks like insert into mytable values(default,?number,?text,?date) select * from mytable where not exists(select * from mytable where int_field = '20') What I'm trying to do is check that the entry is not already present in the database before i insert it. Some help I'm going mad with this. Thanks in advance.

[Mike Dimmick]

From this page[^] it looks like you should simply say INSERT **IGNORE** INTO mytable .... I don't know if that applies to earlier versions of MySQL. If you don't state which database you're using we tend to assume SQL Server 2000. Stability. What an interesting concept. -- Chris Maunder

[Eric Dahlvang]

The syntax is INSERT INTO table1 (field1,field2,field3) SELECT x,y,z FROM table2 WHERE fieldwhatever = 'whatever' You need the same # of fields int he insert as what you select. I don't know what the question marks in your sql are about, but my guess is that they are causing problems. You shouldn't use the word values, but have the field names in the insert into. Also, is it really your intention to insert duplicate rows into the table? Why do you have an exists clause, and you are selecting from the same table? insert into mytable (default,number,text,date) select default,number,text,date from mytable where int_field = '20' If you post your actual code...it would be easier to help you. --EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

[richiemac]

i'm not trying to insert duplicate records i am 'trying' to do the opposite. The '?' act sort of like tags so that the actual values can be put in later. The problem is definately not here as this works fine until i use the exist condition. I'm trying to use the exist to check for records as to not add duplicates. I have solved the problem in the web page that I'm using but was just trying to solve the problem at the database level thus making it more reliable. As the error message is quite obviously pointing to the sql statement, and the fact that a simple insert statement is working completely okay, then I am taking for granted that the problem occurs when i add the 'where not exists' condition. I was therefore just trying to get help with writing these kinds of sql statement. :)

[Eric Dahlvang]

richiemac wrote:

insert into mytable values(default,?number,?text,?date) select * from mytable where not exists(select * from mytable where int_field = '20')

You are inserting into mytable, and yet you are selecting from my table also. This is perfectly legal, however...it will insert duplicates into the table - if that is what you want. Select * from mytable where not exists(select * from mytable where int_field = '20') This will return NO records if there is a value of '20' in int_field for ANY record in mytable. I don't fully grasp what you are trying to do, but look at this example of using exists in an insert into: Three tables named (tblPeople,tblYoungPeople,tblDeadPeople) All three have fields (PeopleID,LastName,FirstName,Age) To fill the tblYoungPeople table with all people under 40 who are not dead, you would do this:

insert into tblYoungPeople (firstname,lastname,age)
select firstname,lastname,age from tblPeople p
where p.age < 40 AND not EXISTS(SELECT * from tbldeadpeople dp WHERE dp.peopleid = p.peopleid)

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

[Eric Dahlvang]

I thought a bit more about what you're trying to do. You want to insert a record into the table if, and only if, there are no records that have int_field = '20'. Something you could do is like this:

insert into mytable (default,number,text,date)
select 'xxx' as default,123 as number,'yyy' as text,'2006/07/14' as date)
from mytable where not exists(select * from mytable where int_field = '20') LIMIT 1

The LIMIT 1 assures that only 1 record will be inserted (because you are selecting only 1 record). The values are aliased in the select statement, because you don't want to pull values from mytable. Using my previous example, this worked:

INSERT INTO tblpeople (firstname,lastname,age)
SELECT 'John' as firsname, 'Williams2' as lastname, 20 as age
FROM tblPeople p
WHERE not EXISTS(SELECT * from tblpeople p2
WHERE p2.firstname = 'John' and p2.lastname='Williams2' and p2.age = 20) LIMIT 1

I hope this helps. --EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

[richiemac]

Thanks for the help Eric. I'm finally getting somewhere! Thanks again buddy!