CODEWITHSUNDEEP
mysqlsql
user9945216
user9945216

Reputation:

INSERT statement add multiple identical rows

i'm using a webservice to add value inside my database, normaly my query should add only one row but actually it had multiple identical rows.

So here is my 2 tables : Application.sql

Details.sql

My query: 

INSERT INTO Details 
(SecurityKey, Name, URL, DateLog, Level, Message, Username, ServerName, Port, ServerAdress, RemoteAdress, Logger, Exception, ApplicationID) 
SELECT  
@SecurityKey, @Name, @URL, @DateLog, @Level, @Message, @Username,@ServerName, @Port, @ServerAdress, @RemoteAdress, @Logger, @Exception, @ApplicationID
FROM Details D
INNER JOIN Application A ON @SecurityKey = A.SecurityKey AND @ApplicationID = A.ID

And my webservice with @ApplicationID=14 and @SecurityKey="shSfnDOIPM": 

{
  "DateLog": "27/02/12",
  "Level": "danger",
  "Message": "ytry",
  "Username": "ghth",
  "ServerName": "zrzrzer",
  "Port": "80",
  "ServerAdress": "36zr",
  "RemoteAdress": "12z",
  "Logger": "rg",
  "Exception": "zr",
  "ApplicationID": 14,
  "Name": "zr",
  "SecurityKey": "shSfnDOIPM",
  "URL": "wwww",
}

Normaly with these informations my query should sort only one row but as you can see in the "Details" picture, it create many and many rows...

Thanks for helps

Upvotes: 0

Views: 81

Answers (2)

SadmirD
SadmirD

Reputation: 662

You are utilizing INSERT INTO SELECT query, yet you don't issue any JOIN to connect these two tables (either implicit or explicit). When utilizing the select query that affects multiple tables, you need to have N-1 where statements to join them (implicit join), or N-1 of such JOINs, in order to avoid cartesian product. Cartesian product = "multiple inserts" in your case, due to INSERT INTO... SELECT

I guess you need an INNER JOIN and would advise you to avoid implicit joins in general.

An example, editing your query:

INSERT INTO Details 
(SecurityKey, Name, URL, DateLog, Level, Message, Username, ServerName, Port, ServerAdress, RemoteAdress, Logger, Exception, ApplicationID) 
SELECT  
@SecurityKey, @Name, @URL, @DateLog, @Level, @Message, @Username,@ServerName, @Port, @ServerAdress, @RemoteAdress, @Logger, @Exception, @ApplicationID
FROM Details D
INNER JOIN Application A ON D.ApplicationID = A.ID 
WHERE A.SecurityKey = @SecurityKey AND A.ID = @ApplicationID

Notice that I put INNER JOIN and moved your previous "join" into a WHERE statement.

Upvotes: 0

Mike
Mike

Reputation: 23

It looks like you are referencing two tables in your query but you are not joining them together. Possibly getting an cartesian product in the process.

Upvotes: 1

Related Questions