No of visitors who read this post: 320
Category: MS SQL Server
Type: Question
Author: Hal665
Your rating: None Average: 5 (1 vote)

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.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

# Comment Id: 20033

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.