It's not so much it doesn't work as it won't compile. That is completely the wrong syntax - see
MySQL INSERT INTO SELECT Explained By Practical Examples[
^]
Drop the
SET OpenOrder.* = OpenOrderTemp.*
and list all of the columns instead of
SELECT OpenOrder.orderId
You will also need to fix the ON clause for the join and list only the columns that link the tables - see
MySQL :: MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Clause[
^]
Edit: I took another look and there are other issues too. Try something like this:
INSERT INTO OpenOrder (symbol,orderid,clientorderid,price)
SELECT ot.symbol,ot.orderid,ot.clientorderid,ot.price
FROM OpenOrderTemp ot
LEFT OUTER JOIN OpenOrder o ON o.symbol=ot.symbol
and o.clientorderid = ot.clientorderid
and o.orderid = ot.orderid
and o.price = ot.price
WHERE o.symbol is null
Points to note:
- I have listed all of the columns I intend to insert and I explicitly name the columns in the correct order in the SELECT part of the query.
- I have used Table aliases (
o
,
ot
) to save having to type out the full table name each time.
- I've used a
LEFT OUTER
join and tested for
NULL
values as we're trying to find values that are not already in OpenOrder, not the stuff that matches - you might find this article helps
Visual Representation of SQL Joins[
^]
- You don't want to insert values from
OpenOrder
- they will null, it's the values in
OpenOrderTemp
that you need
- I've used all of the columns in the join but that is unusual - normally you would have a specific key that would link the tables - in this case probably just
orderid