INSERT INTO WHERE NOT EXIST into the same table

  • Thread starter Thread starter Roy Gourgi
  • Start date Start date
R

Roy Gourgi

Hi,

I am trying to add a row to my table but I get the error message
"invalid column name SOBN and BN1" on this statement. Basically, I am trying
to add the row into the same table that I am searching if it does not find
SOBN = 5 and BN1 =3. What is the problem? Do I have to create a primary
key? Is it possible to have more than 1 primary key (i.e. secondary
....... ).

strCommand = "INSERT INTO tblSQL_2 (SOBN, BN1) SELECT SOBN , BN1 WHERE NOT
EXISTS (SELECT * FROM tblSQL_2 WHERE SOBN = 5 AND BN1 = 3)";

Thanks
Roy
 
strCommand = "INSERT INTO tblSQL_2 (SOBN, BN1) SELECT SOBN , BN1 FROM
tblSQL_2 WHERE NOT
EXISTS (SELECT * FROM tblSQL_2 WHERE SOBN = 5 AND BN1 = 3)";

You forgot from tblSQL_2. Aside from that I have nothing else nice to say
about this so I will refrain.
 
...
I am trying to add a row to my table but I get the
error message "invalid column name SOBN and BN1"
on this statement.
Click to expand...

Without knowing what database you're targeting, I would have guessed that
you rather should have gotten another error message, pointing to the fact
that you don't provide a table name for the "SELECT"

INSERT INTO tblSQL_2 (SOBN, BN1)
SELECT SOBN , BN1
(something is missing here...)
WHERE NOT EXISTS
(SELECT *
FROM tblSQL_2
WHERE SOBN = 5 AND BN1 = 3)

There could also be the case that there isn't any columns named SOBN or BN1
in your table.
Basically, I am trying to add the row into the
same table that I am searching if it does not
find SOBN = 5 and BN1 =3. What is the problem?
Click to expand...

It isn't comprehensible what you're trying to say.

You say that you want to add some rows to a table...

From what table?

The same table? Then I would have to ask you *why*? If you can find them in
that table, then they already *are* in that table...

Say that you want to get the rows from *another* table, then it *could*
work, but consider what it is you really want to do, and what the SQL
statement really does for you.

INSERT INTO tblSQL_2 (SOBN, BN1)
SELECT SOBN , BN1
FROM anotherTable
WHERE NOT EXISTS
(SELECT *
FROM tblSQL_2
WHERE SOBN = 5 AND BN1 = 3)

This will select SOBN and BN1 from *all* rows in anotherTable, but only if
there *isn't* a row in tblSQL_2 with SOBN = 5 and BN1 = 3. If there is any
row in tblSQL_2 with SOBN = 5 and BN1 = 3, then *no* rows will be inserted.
Do I have to create a primary key?
Click to expand...

No, you don't have to, and the question of PKs or not doesn't seem relevant
in what you're trying to do.
Is it possible to have more than 1 primary key (i.e. secondary
Click to expand...

Not in general, though a SQL-compliant database should have the possibility
to add more unique indexes, which would fill about the same purpose as
additional PKs.

However, I don't think that's really the problem here.

// Bjorn A
 
Hi,

What I meant to do is this actually, as I did not understant how it worked
before. This works without the From.
strCommand = "INSERT INTO tblSQL_2 (SOBN ,BN1 ) select 5,3 WHERE not exists
(select * from tblSQL_2 where SOBN = 5 AND BN1 = 3)";

Thanks

Roy
 
It worked when I used the constants 5,3 after the select statement, but what
if I wanted to use variables instead, how would I do that. In other words I
would like to do this.

int var1 = 5;
int var2 = 3;

strCommand = "INSERT INTO tblSQL_2 (SOBN ,BN1 ) values (var1,var2) WHERE not
exists
(select * from tblSQL_2 where SOBN = 5 AND BN1 = 3)";

So basically what I want to do is search the table for SOBN=5 and BN1=3 and
if it does not find them, then I want to add them in the same table. How
would I do that.

Thanks
Roy
 
Hi Roy,

A SQL statement is a string. As such, you can build it to contain any values
you wish. Alternatively, and this is probably a better solution, use a
parameterized Stored Procedure.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
If you push something hard enough,
it will fall over.
- Fudd's First Law of Opposition
 
...
It worked when I used the constants 5,3 after the select
statement, but what if I wanted to use variables instead,
how would I do that. In other words I would like to do this.

int var1 = 5;
int var2 = 3;

strCommand = "INSERT INTO tblSQL_2 (SOBN ,BN1 ) values (var1,var2) WHERE
not exists
(select * from tblSQL_2 where SOBN = 5 AND BN1 = 3)";
Click to expand...

The "easy" solution:

strCommand = "INSERT INTO tblSQL_2 (SOBN, BN1) " +
"values (" + var1 + ", " + var2 + ") "
"WHERE not exists " +
"(select 'x' from tblSQL_2 where SOBN = " +
var1 + " AND BN1 = " + var2 + ")";

The "better" solution involves a parameterized statement, where you simply
have the SQL string with "placeholders" for the values.

OdbcCommand cmd = new OdbcCommand();

...

strCommand = "INSERT INTO tblSQL_2 (SOBN, BN1) " +
"values ( ?, ? ) " +
"WHERE not exists " +
" (select 'x' from tblSQL_2 " +
"WHERE SOBN = ? AND BN1 = ? )";

cmd.CommandText = strCommand;

OdbcParameter par1 = new OdbcParameter();
par1.OracleDbType = OdbcType.Int;
par1.Value = var1;

OdbcParameter par2 = new OdbcParameter();
par2.OracleDbType = OdbcType.Int;
par2.Value = var2;

cmd.Parameters.Add(par1);
cmd.Parameters.Add(par2);
cmd.Parameters.Add(par1);
cmd.Parameters.Add(par2);

(Note 1: Syntax of "placeholders" differ between ADO.NET providers)

(Note 2: I replaced * with 'x' as it "may" increase performance, depending
on which database you're targeting)


// Bjorn A
 
Back
Top