0

I am relatively new to this form and it would be of great help if anyone could clarify how to solve this specific use-case i am facing In My scenario.

I have 2 tables Table_A and Table_B

table_A has 2 fields id, status Table_A (id int, table_a_status int) where id is a primary key.

Table_B has 3 fields id, status Table_A (id int, table_a_id int, table_b_status int) where table_a_id is a foreign key and id is the primary key for Table_B.

    Table_A                      

id       table_a_status          
1        1                       
2        1                       

      Table_B

id   table_a_id table_b_status     
 1   1               1   
 2   1               1
 3   2               1     
 4   2               1

There are many ways to update multiple rows in multiple tables and currently i am using procedures to solve this, but its not quite efficient, I wanted to know an efficient way to update both the tables using a single query. I have tried using the queries:

UPDATE Table_A 
INNER JOIN Table_B ON Table_B.table_a_id = Table_A.id 
SET Table_A.table_a_status = 1, Table_B.table_b_status =1 
WHERE Table_A.id IN (1,2);

UPDATE Table_A, Table_B 
SET Table_A.table_a_status = 1, Table_B.table_b_status =1 
WHERE Table_B.table_a_id = Table_A.id  AND Table_A.id IN (1,2);

But both the queries are giving an error if i have a condition where i am updating for 2 id's (1,2) Operand code should contain 1 column, but it works when i update one id at a time eg)

UPDATE Table_A, Table_B 
SET Table_A.table_a_status = 1, Table_B.table_b_status =1 
WHERE Table_B.table_a_id = Table_A.id  AND Table_A.id IN (1);

I know Operand code should contain 1 column error is invoked because the sub-query is returning more than one row in multiple table.

Is there any other way to update multiple rows in multiple tables at the same time

Barmar
  • 741,623
  • 53
  • 500
  • 612
Walter
  • 80
  • 5
  • 1
    Possible duplicate of [MySQL, update multiple tables with one query](https://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query) – Raymond Nijland Jan 23 '19 at 21:36
  • 1
    You don't have any subqueries, I'm not sure why you're getting that error. They look just like the answers in the linked question. – Barmar Jan 23 '19 at 22:41
  • Your query works fine for me: https://www.db-fiddle.com/f/ot3cR8WLDe5Mkv6Zkzu5iZ/0 – Barmar Jan 23 '19 at 22:45
  • Even i am looking into why is that query not working. Still cant find the reason. But thank you for your help – Walter Jan 24 '19 at 13:21
  • I found out the Issue. In my server Mysql is running is safe Update mode. Its giving me an error that i am not using Key columns. I know how to disable safe update, But is it a good practise to do that..... – Walter Jan 24 '19 at 14:06
  • Found a solution :). Use a procedure with the same query. Turn off safe update in the beginning and after the update is finished Turn of the safe update again..... Even though this is not the ideal way, it still worked. Thank you for you Input @Barmar – Walter Jan 24 '19 at 15:22

0 Answers0