CODEWITHSUNDEEP
c#sql
kandege rajika
kandege rajika

Reputation: 111

insert and update sql statement in single sql statement

I want to create insert and update sql statements in single statement. But insert statement is for one table and update statement for another table...

this is Appliance_Location table

CREATE TABLE [dbo].[Appliance_Location] (
[Appliance_Id]  NCHAR (10)    NOT NULL,
[RoomId]        NVARCHAR (20) NOT NULL,
[ApplianceName] NCHAR (10)    NOT NULL,
[AddDate]       DATE          NULL,
CONSTRAINT [PK_Appliance_Location] PRIMARY KEY CLUSTERED ([Appliance_Id] ASC)

);

and this is Appliance_Count table

CREATE TABLE [dbo].[Appliance_Count] (
[RoomId]   NVARCHAR (20) NOT NULL,`enter code here`
[Bulb]    INT           NOT NULL,
[Fan]     INT           NOT NULL,
[AC]       INT           NOT NULL,
[Computer] INT           NOT NULL,
CONSTRAINT [PK_Appliance_Count] PRIMARY KEY CLUSTERED ([RoomId] ASC)

);

when I insert an appliance to the Appliance_Location table then count of that particular appliance in Appliance_Count table should be updated

Upvotes: 1

Views: 127

Answers (2)

Yeldar Kurmangaliyev
Yeldar Kurmangaliyev

Reputation: 34189

Not sure how about other SQL servers, but you are allowed to execute multiple queries using the same syntax in MS SQL.

using (SqlConnection connection = CreateConnection())
{   
    SqlCommand command = connection.CreateCommand();
    command.CommandText = @"INSERT INTO `TableA` VALUES (1, 2, 3); 
UPDATE `TableB` SET [Val] = 'value' WHERE [Id] > 10";
    command.CommandType = CommandType.Text;
    // ... other initializations

    return command.ExecuteNonQuery();
}

You even can return values:

INSERT INTO `TableC` VALUES (1, 'value');
SELECT SCOPE_IDENTITY() AS Id;

After this query you can get SQL reader and read "Id" value from the response.

Why don't you just try before asking?

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

It is not possible to insert and update in single query. But You have one option for this task. You can create trigger to inserting of one table. And trigger through update value in other table.

Upvotes: 2

Related Questions