Use of NOT EXIST clause is not giving satisfying results

Asked By Hal665 10 points N/A Posted on - 06/24/2011

Hi,

I have to fix a program error, which should find those entries in table1 which do not have matching records in table2 and then insert records in table2, so that for each record in table1 there should be at least one corresponding match in table2.

First I am trying to return IDs in table1, which do not have a corresponding match in the second table and then want to insert. Below is the query which is not working as per expectation:

INSERT into Services(Services.FkLobbyId, Services.FkServiceId, Services.ServiceTime)
values (LobbyId, '26','1')
(Select LobbyId FROM Lobby WHERE (NOT EXISTS (SELECT FkLobbyId FROM ServiceProvided WHERE (Lobby.LobbyId = FkLobbyId))))

Thank you.

• Status: Open
• Question Views: 841
• Answer Count: 1
• Vote Up 1 Vote Down

• Answer Accepted: No
• Question Category: MS SQL Server

Answered By Mehboob-Ul-Haque 0 points N/A #129304

Use of NOT EXIST clause is not giving satisfying results

INSERT into Services(Services.FkLobbyId, Services.FkServiceId, Services.ServiceTime)
values (LobbyId, '26','1')
(Select LobbyId FROM Lobby WHERE (NOT EXISTS (SELECT FkLobbyId FROM ServiceProvided WHERE (Lobby.LobbyId = FkLobbyId))))

This query is wrongly written therefore an error message is being displayed.

The correct query is as follows:

INSERT into Services(Services.FkLobbyId, Services.FkServiceId, Services.ServiceTime)
values (LobbyId=(Select LobbyId FROM Lobby WHERE LobbyId NOT in (SELECT FkLobbyId FROM ServiceProvided )), '26','1') )

Hope your problem will be solved after applying this query.

Regards.

About Mehboob-Ul-Haque

Questions
0

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 07/25/2011
• Question Category: MS SQL Server