1

I have 2 tables : Pur_Entry and Pur_order

Pur_entry => id, name, id_pur_order, is_deleted
Pur_order => id, date, is_deleted

I have to update 'is_delete' in both tables at one action.

Is there any inner join method for these?

Mirza Obaid
  • 1,707
  • 3
  • 23
  • 35
  • No, Update can only update columns in one table at a time, but you can send two updates to the database in one batch, in one transaction. – Charles Bretana Aug 28 '17 at 12:54
  • @CharlesBretana This is not true for MySQL. MySQL supports updating multiple tables in a single statement. – Siyual Aug 28 '17 at 12:56
  • @CharlesBretana so basically I have to use stored procedure right? –  Aug 28 '17 at 13:05
  • based on comment from @Siyual, I did some research. I was wrong. Check out https://dev.mysql.com/doc/refman/5.7/en/update.html - second form "Multiple table Update syntax". You can ignore my previous comment. Siyual, just curious, why not also provide the link to the docs in your comment? – Charles Bretana Aug 28 '17 at 14:39
  • @CharlesBretana Honestly, I was on mobile and didn't want to deal with copying links lol. You're right, I should have, though. – Siyual Aug 28 '17 at 16:42
  • Hah! no worries, I was just wondering... – Charles Bretana Aug 28 '17 at 19:34

2 Answers2

0
UPDATE pur_order a, pur_entry b SET a.is_deleted = 1, b.is_deleted = 1
WHERE b.id_pur_order = b.id AND a.id = 1;
Roman
  • 102
  • 1
  • 4
-1

This topic has been asked already before.
Have a look here: MySQL, update multiple tables with one query

In your case, this should work:

UPDATE Pur_order, Pur_entry
   SET Pur_order.is_deleted = 1
      ,Pur_entry.is_deleted = 1
 WHERE Pur_entry.id_pur_order = Pur_order.id
   AND Pur_order.id = @YourDesiredUpdateId
;
Esteban P.
  • 2,789
  • 2
  • 27
  • 43
  • but this will update all the fields, I have condition on pur_entry->id –  Aug 28 '17 at 13:09
  • @mauliksompura of course you have to specify the row you want to update. i just updated my answer. Variable YourDesiredUpdateId should hold the Id you want to update. or how do you specify the rows you want to update? – Esteban P. Aug 28 '17 at 13:13
  • UPDATE Pur_order, Pur_entry SET Pur_order.is_deleted = 1 ,Pur_entry.is_deleted = 1 WHERE Pur_entry.id_pur_order = Pur_order.id AND Pur_entry.id = @id –  Aug 28 '17 at 13:21
  • 2
    If the question is a duplicate, flag it as such. – Siyual Aug 28 '17 at 13:21