1

I have two tables 1->CompanyMaster_tbl in this having Two fields . Cid and CompanyName,

Cid is the primary key of this table

2->DepartmentMaster_tbl in this having 4 fields. dtid,dtname,dtphon,dtmail,Cid.

dtid is the primary key,and Cid is the foreign key

I have two update query like this:

UPDATE CompanyMaster_tbl SET CompanyName = @CompanyName WHERE Cid = @Cid

update DepartmentMaster_tbl set dtName = @dtName,dtPhone = @dtPhone,dtEmail = @dtEmail  where dtId=@dtid

is there any way to write this two query in single line with any of our join condition

Liath
  • 9,913
  • 9
  • 51
  • 81
user3106114
  • 183
  • 2
  • 11
  • 31
  • 2
    I dont think you can update 2 seperate tables. look at answer here http://stackoverflow.com/a/2044520/1692632 – Darka Jan 07 '14 at 08:23
  • Please clarify how you would like to join the tables. In the example, you have two variables, \@Cid and \@dtid, which are used to determine records to be updated. If those variables are not connected, the records are not connected either, which excludes using a JOIN. However, if those variables are connected, pls explain that, so that we know what is to be joined. – Bartosz Klimek Jan 07 '14 at 08:23
  • i cant update 2 separate tables together in sql server – user3106114 Jan 07 '14 at 08:25
  • can you create a meaningful view with both tables? – sqlab Jan 07 '14 at 08:27
  • Cid is primary key in CompanyMaster_tbl and Cid is foreign key in DepartmentMaster_tbl and dtid is the primary key DepartmentMaster_tbl – user3106114 Jan 07 '14 at 08:29

1 Answers1

0

Not sure what you mean by

is there any way to write this two query in single line with any of our join condition

but the code looks fine to me.
If you add some more information about what is bothering you with your existing code, or what you are trying to accomplish, I will modify this answer...

Edit: (As per @user3106114 comment)
You can do both quesries in 1 line if you wrap them in a Stored procedure.

Community
  • 1
  • 1
Avi Turner
  • 10,234
  • 7
  • 48
  • 75
  • in above i wrote two separate query,,i want to write that as a single qeury,, – user3106114 Jan 07 '14 at 08:20
  • @user3106114 What is your `Join condition`? – Avi Turner Jan 07 '14 at 08:26
  • i can write somthing like this: UPDATE CompanyMaster_tbl SET CompanyName = 'IBS' , d.dtName='ss' from CompanyMaster_tbl c join DepartmentMaster_tbl d on c.Cid= d.cId where c.Cid = 3 and d.dtId= 2 – user3106114 Jan 07 '14 at 08:30
  • As @Darka pointed out, it is not possible to update 2 tables in a single row. You can wrap them up in a Stored procedure, or make sure they both are executed or rolled back upon failure using a transaction. – Avi Turner Jan 07 '14 at 08:49