Update when same table in Select with additional joins

Question

My previous question was marked as duplicate but I can't find the answer I'm looking for in there likely because my case is more complex.

If necessary I'll make a SqlFiddle but for now the question is that I am doing something like this:

Select if(A.Value>10,concat(A.Field1,B.FIeld),concat(A.Field1,C.Field1)) 
from A
Inner Join B on A.Field3=B.Field3
Inner Join C on A.Field3=C.Field3

Trying to use any of the options from the dupe question I point to fails as it means I'm doing another join to this select which, at least for me is impossible and my ultimate update goal here is:

Update A set Display=(Select if(A.Value>10,concat(A.Field1,B.FIeld),concat(A.Field1,C.Field1)) 
from A
Inner Join B on A.Field3=B.Field3
Inner Join C on A.Field3=C.Field3)

You did not follow the multi-table update syntax demonstrated in the duplicate topic. — Shadow, Jul 09 '18 at 12:59

score 0 · Answer 1 · answered Jul 09 '18 at 13:00

Do you just want syntax for an update join in MySQL?

UPDATE A
FROM A
INNER JOIN B
    ON A.Field3 = B.Field3
INNER JOIN C
    ON A.Field3 = C.Field3
SET Display = CASE WHEN A.Value > 10
                   THEN CONCAT(A.Field1, B.Field)
                   ELSE CONCAT(A.Field1,C.Field1) END;

Update when same table in Select with additional joins

1 Answers1