mysql syntax error with inner join on two tables

Question

I have this sql query:

update edi_file_steps 
set 
    table_A.user_id= table_B.id ,
    table_A.message= SUBSTRING_INDEX(table_A.message,'[',1)
FROM 
    edi_file.steps AS table_A INNER JOIN GU_User as table_B
where 
   message LIKE '%Downloaded%'AND table_B.login = 'Jack'

But I am getting mysql syntax error. Is there a problem with my syntax? I am using mysql 5.7.

' Is there a problem with my syntax' - yes it's not mysql syntax..for an update..join. please review multi table update in manual .dev.mysql.com/doc/refman/8.0/en/update.html also a join (any join) would normally have an ON clause 'INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table' - dev.mysql.com/doc/refman/8.0/en/join.html — P.Salmon
– P.Salmon, Commented Apr 2, 2020 at 12:07

Tum · Accepted Answer · 2020-04-02 11:53:16Z

3

You can't use FROM in an UPDATE query, you specify the table after the UPDATE statement:

UPDATE edi_file_steps table_A
INNER JOIN GU_User AS table_B
SET 
    table_A.user_id= table_B.id ,
    table_A.message= SUBSTRING_INDEX(table_A.message,'[',1)
WHERE 
    message LIKE '%Downloaded%'AND table_B.login = 'Jack'

answered Apr 2, 2020 at 11:53

Tum

7,6852 gold badges27 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

mysql syntax error with inner join on two tables

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