Solved

difference between two similar tables

Posted on 2012-12-29
4
341 Views
Last Modified: 2013-01-12
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
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 38730768
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_new
EXCEPT
SELECT column_that_should_be_unique FROM production.recipes
;

Open in new window


Here is an alternative that will work in most database systems.

SELECT column_that_should_be_unique 
FROM recipes_new n
WHERE NOT EXISTS (
    SELECT 1
    FROM production.recipes o
    WHERE o.column_that_should_be_unique = n.column_that_should_be_unique 
)
;

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.
0
 

Author Comment

by:rgb192
ID: 38749868
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
)
;


no results returned
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 38750765
As I said, EXCEPT is a SQL Server syntax that I did not believe is available in MySQL, so the first result I expected. The second indicates to me that the query ran, but there are no rows that belong in the new table that are not in the original.

Are you expecting some to be there?

If so, the issue may be that the id does not properly match rows. For example, if id is an autonumber, you will successfully match every row if both tables have the same number of rows (i.e., 1 = 1). However, if row one for one table has different values than row one for the other, you really have a non-existent row. Therefore, the trick is to find the column or columns that accurately identify a row.
0
 

Author Closing Comment

by:rgb192
ID: 38770179
the where not exists works when I add rows into the first table

thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question