CODEWITHSUNDEEP
c#sql.netsql-server
Detilium
Detilium

Reputation: 3034

SQL - Insert into statement with subquery, then update statement

I've been struggling with an SQL statement for a while now, and I can't seem to wrap my head around the problem.

I want to insert a new record in table A using a SELECT subquery. When this is done, I want to update a record in table B.

Here is what I've tried (dummy SQL, look-alike):

INSERT INTO [A] (ID, Expiration, Type) 
VALUES ({some-id}, (
    SELECT [B].[Expiration], [B].[Type] FROM [B] 
    WHERE [B].ID = {other-id}))

When running the statement, I get the following error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

I'm not quite sure where the error exists, but I suspect that this has something to do with my where clause in the select

EDIT This query is run from within a .net solution. I have the query setup like this:

string insertStatement = "INSERT INTO [A] (ID, Expiration, Type) "
    + "VALUES (@someId, (SELECT [B].[Expiration], [B].[Type] "
    + "WHERE [B].ID = @otherId))";

command.CommandText = insertStatement;
command.AddParameter("@someId", someId);
command.AddParameter("@otherId", otherId);
command.ExecuteNonQuery();

Upvotes: 0

Views: 662

Answers (3)

Er Ketan Vavadiya
Er Ketan Vavadiya

Reputation: 283

try this.. 

INSERT INTO A(ID,Expiration,Type)
SELECT @SOME_ID,[B].[Expiration], [B].[Type]
FROM [B]
WHERE [B].ID = {other-id}

it should work for you ..

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Move your @someId variable to inside the Select query.

Try this

string insertStatement = "INSERT INTO [A] (ID, Expiration, Type) "
    + "(SELECT @someId, [B].[Expiration], [B].[Type] "
    + "WHERE [B].ID = @otherId)";

command.CommandText = insertStatement;
command.AddParameter("@someId", someId);
command.AddParameter("@otherId", otherId);
command.ExecuteNonQuery();

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Try this

INSERT INTO [A] (ID, Expiration, Type)
    SELECT {some-id},[B].[Expiration], [B].[Type] FROM [B] 
    WHERE [B].ID = {other-id}

Upvotes: 1

Related Questions