CREATE TABLE recipes_new LIKE production.recipes; INSERT recipes_new SELECT * FROM production.recipes;
identical tables
and then recipes_new inserts a new value
is there a diff command
so I can see difference between recipes and recipes_new
MySQL Server
Last Comment
rgb192
8/22/2022 - Mon
Kevin Cross
I cannot recall using EXCEPT in MySQL. EXCEPT in SQL Server lets you see the values in the first table that are not in the second. This is not available in MySQL to my knowledge, but you can give it a try.
SELECT column_that_should_be_unique FROM recipes_newEXCEPTSELECT column_that_should_be_unique FROM production.recipes;
Here is an alternative that will work in most database systems.
SELECT column_that_should_be_unique FROM recipes_new nWHERE NOT EXISTS ( SELECT 1 FROM production.recipes o WHERE o.column_that_should_be_unique = n.column_that_should_be_unique );
It multiple columns are necessary to match-up rows, then just alter the WHERE clause accordingly. Remember that an auto-numbered field is not sufficient as the two tables will increment at different paces; therefore, the same row in both tables may have a different id if it is a simple incrementing integer.
rgb192
ASKER
SELECT id FROM recipes_new
EXCEPT
SELECT id FROM .recipes
;
Error Code: 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 'SELECT id FROM recipes' at line 3
SELECT id
FROM recipes_new n
WHERE NOT EXISTS (
SELECT 1
FROM recipes o
WHERE o.id = n.id
)
;
Open in new window
Here is an alternative that will work in most database systems.
Open in new window
It multiple columns are necessary to match-up rows, then just alter the WHERE clause accordingly. Remember that an auto-numbered field is not sufficient as the two tables will increment at different paces; therefore, the same row in both tables may have a different id if it is a simple incrementing integer.