CODEWITHSUNDEEP
mysqlsqlinner-join
Godspower chimezie
Godspower chimezie

Reputation: 13

sql query error using INNER JOIN

#1064 - 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 tb_users INNER JOIN tb_ph ON tb_ph.username=tb_users.username WHERE tb_ph.r' at line 1

I get this error above when trying to run the query below

UPDATE tb_users 
   SET tb_users.tgh = tb_ph.readygh,
       tb_users.readygh = tb_ph.readygh * 0.25,
       tb_users.profitbalance = tb_ph.readygh - (tb_ph.readygh * 0.25) 
FROM tb_users 
INNER JOIN tb_ph ON tb_ph.username=tb_users.username 
WHERE tb_ph.readygh = tb_ph.paket + (tb_ph.paket*0.6) 
    and tb_users.username=tb_ph.username

How can it be sorted out?

Upvotes: 1

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

The correct syntax in MySQL is:

UPDATE tb_users u INNER JOIN
       tb_ph p
       ON p.username = u.username 
    SET u.tgh = p.readygh,
        u.readygh = p.readygh * 0.25, 
        u.profitbalance = p.readygh - (p.readygh * 0.25) 
WHERE p.readygh = p.paket + (p.paket*0.6);

Notes:

  • Your syntax is SQL Server syntax, not MySQL syntax.
  • Table aliases make the query easier to write and to read.
  • There is no need to repeat the join condition in both the ON clause and the WHERE clause.
  • The WHERE condition is highly suspicious. Normally, you don't use equality on floating point values, because very small rounding errors could make "equal" values fail an equality comparison.

Upvotes: 1

Related Questions