sql query error using INNER JOIN

Question

#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?

Gordon Linoff · Accepted Answer · 2017-05-31 01:47:57Z

1

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.

answered May 31, 2017 at 1:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql query error using INNER JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related