CODEWITHSUNDEEP
c#mysqlsubquery
Ben
Ben

Reputation: 2523

Nested SELECT in an INSERT Query

These are my tables:

 Equipment
-------------
 ID INT (PKey)
 Name CHAR(10)
 Type CHAR(10)
 LocationID INT (FKey)
 Description TEXT

 Location
-------------
 LocationID INT (PKey)
 Name CHAR(10)
 Code INT

The user is given a list of Location.Code to select when inputing new Equipment. (This is how the user identifies the Equipment.LocationID.)

However if I input the data for Location.Code as Equipment.LocationID it will break the system. Is there better way of doing this? For example can I come up with a way to select the Location.LocationID and use the in place of Equipment.LocationID?

NOTE I can't change what the user selects, that has to remain the same.

For example (pseudo code):

string userInput = "110"; // Location.Code
SELECT LocationID FROM Location WHERE Code = @userInput;

userInput = LocationID; // LocationID = 1;
INSERT INTO Equipment(LocationID) VALUES (@userInput);

Is this the best way or is there a more efficient way?

Upvotes: 0

Views: 150

Answers (1)

Elixir Techne
Elixir Techne

Reputation: 1856

merge two queries into one as below

INSERT INTO Equipment(LocationID) SELECT LocationID FROM Location WHERE Code = '110';

Upvotes: 1

Related Questions