how to insert values to table only if the same row not exists

• ofirhgy

  Grasshopper

  Points: 13

  More actions

  October 7, 2012 at 8:01 am

  #282707

  hay....

  I want insert to table (table name is: "portfolioForExpert") 2 values:

  1) expertID

  2) portfolioID (value of "1" represent: "stocks", value of "2" represent" "bonds")

  but I want it only if the combination of these two value does'nt exists in the table yet...

  so I try this command: (by the way I use webMatrix+asp.net so "INSERT IGNORE" will not work...)

  updateQuery="INSERT INTO portfolioForExpert(ExpertID,portfolioID)VALUES(@0,'2')WHERE NOT EXISTS(SELECT * FROM portfolioForExpert WHERE ExpertID=@0 AND portfolioID='2')";

  db.Execute(updateQuery,ExpertToEdit.ToString());

  but I get error like this:

  "There was an error parsing the query. [ Token line number = 1,Token line offset = 67,Token in error = WHERE]" (I think its means about the first WHERE....But I don't sure..)

  I try everything but I do'nt find a problem with that...

  could somebody help me please?

  Thanks in advance!!

  ofir.

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  October 7, 2012 at 8:43 am

  #1546500

  ofirhgy (10/7/2012)

  ---

  hay....

  I want insert to table (table name is: "portfolioForExpert") 2 values:

  1) expertID

  2) portfolioID (value of "1" represent: "stocks", value of "2" represent" "bonds")

  but I want it only if the combination of these two value does'nt exists in the table yet...

  so I try this command: (by the way I use webMatrix+asp.net so "INSERT IGNORE" will not work...)

  updateQuery="INSERT INTO portfolioForExpert(ExpertID,portfolioID)VALUES(@0,'2')WHERE NOT EXISTS(SELECT * FROM portfolioForExpert WHERE ExpertID=@0 AND portfolioID='2')";

  db.Execute(updateQuery,ExpertToEdit.ToString());

  but I get error like this:

  "There was an error parsing the query. [ Token line number = 1,Token line offset = 67,Token in error = WHERE]" (I think its means about the first WHERE....But I don't sure..)

  I try everything but I do'nt find a problem with that...

  could somebody help me please?

  Thanks in advance!!

  ofir.

  This doesn't look like T-SQL. Which RDBMS are you using?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• nuberfin

  SSC Enthusiast

  Points: 139

  More actions

  October 7, 2012 at 1:57 pm

  #1546521

  If you want to do it in one statement, you could try this....

  INSERT INTO portfolioForExpert(ExpertID,portfolioID)

  SELECT DISTINCT @0,'2'

  FROM portfolioForExpert

  WHERE NOT EXISTS(

  SELECT 1

  FROM portfolioForExpert

  WHERE ExpertID=@0

  AND portfolioID='2'

  )

  Or, simply wrap an IF...THEN around it like so....

  IF NOT EXISTS (SELECT 1 FROM portfolioForExpert WHERE ExpertID=@0 AND portfolioID='2')

  BEGIN

  INSERT INTO portfolioForExpert(ExpertID,portfolioID)

  VALUES (@0,'2')

  END

• DiverKas

  SSCrazy

  Points: 2049

  More actions

  October 9, 2012 at 10:26 am

  #1547286

  If it is indeed SQL Server... I would think a long look at the MERGE INSERT/UPDATE command would be beneficial.

  Very handy 😀