Difference 2 table in mysql and delta put inside of master table

Question

0.00/5 (No votes)

See more:

I have two tables "OpenOrder" and "OpenOrderTemp". I want look the difference and result insert in "OpenOrder", so I tried with this code but it doesn't work.

SQL

INSERT INTO  OpenOrder (orderId) 
SELECT OpenOrder.orderId 
FROM OpenOrder 
INNER JOIN OpenOrderTemp ON OpenOrder.* = OpenOrderTemp.* 
SET   OpenOrder.* = OpenOrderTemp.*
WHERE OpenOrder.* <> OpenOrderTemp.*;

example data OpenOrder

|symbol|orderid|clientorderid|price|etc....|
-------------------------------------------
|MDTUSD|1223156|web_121354542|42.00|
|REBUSD|1224578|web_128875666|22.00|
....

example data OpenOrderTemp

|symbol|orderid|clientorderid|price|etc.. |
------------------------------------------
|MDTUSD|1223156|web_121354542|42.00|
|REBUSD|1224578|web_128875666|22.00|
|ORNBTC|2585582|web_128545456|02.00|
....

I want to update OpenOrder with only this |ORNBTC|2585582|web_128545456|02.00|

I want to insert all fields.

What I have tried:

i tried this but unlucky not work

SQL

INSERT INTO  OpenOrder (orderId) 
SELECT OpenOrder.orderId 
FROM OpenOrder 
INNER JOIN OpenOrderTemp ON OpenOrder.* = OpenOrderTemp.* 
SET   OpenOrder.* = OpenOrderTemp.*
WHERE OpenOrder.* <> OpenOrderTemp.*;

Posted 3-Jul-22 23:14pm

armanvaneld

Updated 4-Jul-22 0:51am

CHill60

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-07-03T23:29:00

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:

SQL

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