INSERT INTO WHERE NOT EXIST into the same table

[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

[Bjorn Abelli]

"Roy Gourgi" wrote...

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.
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?
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?
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

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

[carion1]

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.
--

Derek Davis
dd******@gmail.com

"Roy Gourgi" <ro***@videotron.ca> wrote in message
news:cj********************@weber.videotron.net...

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

[Roy Gourgi]

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

"Roy Gourgi" <ro***@videotron.ca> wrote in message
news:cj********************@weber.videotron.net...

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

[Roy Gourgi]

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

"Roy Gourgi" <ro***@videotron.ca> wrote in message
news:cj********************@weber.videotron.net...

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

[Kevin Spencer]

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

"Roy Gourgi" <ro***@videotron.ca> wrote in message
news:wp****************@weber.videotron.net...

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

"Roy Gourgi" <ro***@videotron.ca> wrote in message
news:cj********************@weber.videotron.net...

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

[Bjorn Abelli]

"Roy Gourgi" wrote...

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)";

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