SQL Needed to Compare Two Tables

[Guest_imported]

Let's say that I have two tables that are the same except for one record that has been updated in one of the tables. What would be the SQL needed to find just that updated record?

 

[Guest_imported]

if you know what field has been updated:

select 'fieldname' from updatedtable a
where a.fieldname not in (select fieldname from othertable)

 

[karluk]

Hmm, that approach won't work if the updated column has been changed to a value that's in some other row of the table. You need something like

select * from updated_table
minus
select * from other_table [sig][/sig]

 

[MalcolmW]

The syntax will vary depending on your database.
Oracle and others use MINUS, DB2 and others use EXCEPT, and SQL Server and others have a rather strange construction that you plug into the WHERE clause
SELECT *
FROM Updated_Table
WHERE NOT EXISTS
(SELECT * FROM Other_Table)
[sig][/sig]

 

[karluk]

I tried the

select * from updated_table
minus
select * from other_table

syntax in SQL Server 7 and it seemed to work just fine, so probably you won't need to resort to Malcolm's suggestion. Are there any problems with using the "minus" operator that I'm not aware of? [sig][/sig]

 

[MalcolmW]

I tried

select * from sometable
minus
select * from sometable

in SQL Server 7.0, and got the same result set as if I had run

select * from sometable
union all
select * from sometable

(ie I got two copies of the table in both cases)
So I suspect the support for the MINUS function is at best incomplete or varies with different builds of SQL Server 7.
[sig][/sig]

 

[karluk]

Yes, Malcolm is right. There appear to be some serious errors in the "minus" operator in SQL Server. Better avoid it until they get the bugs out. I did three different queries and they all returned unexpected results:

select * from mytable
minus
select * from mytable
appears to do two executions of "select * from mytable".

select * from mytable
minus
select * from mytable
where id# > 100
appears to execute the second select and ignore the first.

select * from mytable
where id# > 100
minus
select * from mytable
generates a syntax error.

[sig][/sig]

 

[ludmart]

The "minus" is not an operator in SQL Server. The following:

select * from updated_table
minus
select * from other_table

simply means that the minus is a table alias for the table of the firts select statement. This is therefore simply two select statments, and not what is required. [sig][/sig]