0

I've got an error saying that" You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM table_A INNER JOIN table_B ON table_A.name = table_B.name' at line 1 "

The sql query is :

$sql="UPDATE table_A SET table_A.quantity = table_A.quantity -  
table_B.quantity FROM table_A INNER JOIN table_B ON table_A.name = table_B.name 
WHERE table_B.status = 'APPROVED'";

Please help me out of this error. Thank you.

Kasut Pink
  • 3
  • 4

2 Answers2

0

"INNER" should be "INNER JOIN"

I think you also have to take out "table_A" from "SET quantity"

 $sql="UPDATE table_A SET quantity = table_A.quantity -  
    table_B.quantity FROM table_A INNER JOIN table_B ON table_A.name = table_B.name 
    WHERE table_B.status = 'APPROVED'";
manderson
  • 837
  • 1
  • 6
  • 18
  • but, I've got this : Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in line $count=mysql_num_rows($sql); – Kasut Pink Mar 11 '17 at 04:09
0

You have syntax error.

Correct syntaxs for UPDATE with INNER JOIN:

UPDATE T1,T2
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

Query:

$sql="UPDATE table_A 
INNER JOIN table_B ON table_A.name = table_B.name 
SET table_A.quantity = table_A.quantity - table_B.quantity
WHERE table_B.status = 'APPROVED'";
Danilo Bustos
  • 1,083
  • 1
  • 7
  • 9