insert and update sql statement in single sql statement

Question

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

What have you done so far?

Ben
– Ben

2015-06-15 03:55:04 +00:00
Commented Jun 15, 2015 at 3:55 — Ben
– Ben, Commented Jun 15, 2015 at 3:55
Check MERGE statement ;).

shA.t
– shA.t

2015-06-15 04:07:05 +00:00
Commented Jun 15, 2015 at 4:07 — shA.t
– shA.t, Commented Jun 15, 2015 at 4:07

Mukesh Kalgude · Accepted Answer · 2015-06-15 03:58:11Z

2

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.

answered Jun 15, 2015 at 3:58

Mukesh Kalgude

4,8542 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yeldar Kurmangaliyev · Accepted Answer · 2015-06-15 04:08:19Z

0

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?

answered Jun 15, 2015 at 4:08

Yeldar Kurmangaliyev

34.3k13 gold badges64 silver badges104 bronze badges

2 Comments

Yeldar Kurmangaliyev Over a year ago

What do you mean by "didn't work"? What happened? Is it a error?

kandege rajika Over a year ago

It doesn't show an error but insert is work and update is not work

Collectives™ on Stack Overflow

insert and update sql statement in single sql statement

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related