How to update data in multiple tables at a time using sql

• ashalatha.cse76

  SSChasing Mays

  Points: 612

  More actions

  June 23, 2014 at 5:09 am

  #290326

  function updatecategory($cat_sub_cat_id)

  {

  echo $cat_sub_cat_id;

  $this->db->trans_start();

  $data=array(

  'cat_id'=>$this->input->post('cat_id'),

  'name'=>$this->input->post('mainc'),

  );

  $this->db->where('cat_id',$cat_sub_cat_id);

  $this->db->update('category',$data);

  $data=array(

  'sub_cat_id'=>$this->input->post('sub_cat_id'),

  'name'=>$this->input->post('subc'),

  );

  $this->db->where('sub_cat_id',$cat_sub_cat_id);

  $this->db->update('sub_category',$data);

  $data=array(

  'cat_id'=>$this->input->post('cat_id'),

  'sub_cat_id'=>$this->input->post('sub_cat_id'),

  );

  $this->db->where('cat_sub_cat_id',$cat_sub_cat_id);

  $this->db->update('category_subcategory_association',$data);

  $this->db->trans_complete();

  }

  how to update data from multiple tables at a time can anyone help me out.

  Actually iam having three tables

  Category

  select category

  cat_id(unique) and name(varchar)

  sub-category

  select sub-category

  sub_cat_id(unique) and name(varchar)

  Association

  select association

  cat_id(unique) and sub_cat_id(unique)

  Here i will be getting the data from association table

  while updating the data if i update cat_id,name it should be updated both in category and asscoiation table in the same way sub-category also can anyone help me how to do this.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 23, 2014 at 5:29 am

  #1724017

  looks like you posted actual or peudocode from MySQL, I'm guessing?

  In SQL Server, you'd have to use a procedure or a batch of code; functions are reserved for read access only.

  it's basically three update statments together.

  BEGIN TRAN

  insert into category(columnList) SELECT columnList [FROM SomeSource]

  insert into subcategory(columnList) SELECT columnList [FROM SomeSource]

  insert into association (columnList) SELECT columnList [FROM SomeSource]

  COMMIT TRAN

  or, if the data already existed, it's classic update statements

  BEGIN TRAN

  UPDATE category SET description = 'someDescription' WHERE catid = 42

  UPDATE subcategorySET description = 'someDescription',catid = 42 WHERE subcatid = 68

  UPDATE association SET catid =42,subcatid = 68 WHERE aid= 32

  etc etc

  COMMIT TRAN

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ashalatha.cse76

  SSChasing Mays

  Points: 612

  More actions

  June 23, 2014 at 5:34 am

  #1724019

  actually category and subcategory will not be having foreign keys these two tables ids we are inserting into association table as i mentioned above.

  As i tried separtely it is refreshing the same page it is not updating.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 23, 2014 at 5:42 am

  #1724021

  Lowell (6/23/2014)

  ---

  looks like you posted actual or peudocode from MySQL, I'm guessing?

  It's PHP.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• ashalatha.cse76

  SSChasing Mays

  Points: 612

  More actions

  June 23, 2014 at 5:47 am

  #1724022

  ha its php codeigniter