0

Scenario: I created and updated a table so that the new table (N) has some rows that are different from the previous table (P). Now I want to find the rows that have been updated.

I'm running the following now:

SELECT N.* 
FROM previous_table P 
JOIN new_table N 
  ON P.item_id = N.item_id 
WHERE (P.column_1 != N.column_1) 
   OR (P.column_2 != N.column_2) 
   OR ... (P.column_n != N.column_N);

Right now I only have three columns, so it's working for me but I imagine this method is rather untenable if the number of columns is very large.

Is there a way to simplify the WHERE bit?

  • this method is rather untenable if the number of columns is very large In any case you must compare all fields. So you must write whole fields list. – Akina Nov 14 '18 at 17:28
  • So there is no other way but to explicitly list each column? – lardandweed Nov 14 '18 at 17:30
  • There is a lot of variants, but all of them (exclusion - export data to external CSV files) needs to list each column. – Akina Nov 14 '18 at 17:33
  • I see. Looks like there's no two ways about it then. Cheers! – lardandweed Nov 14 '18 at 17:35
  • Can there be rows in P that are missing from N, as in 'deleted'? – Rick James Nov 14 '18 at 20:06
1
SELECT N.* 
FROM new_table N 
LEFT JOIN previous_table P 
     ON P.item_id = N.item_id 
    AND P.column_1 = N.column_1
    AND P.column_2 = N.column_2
    AND /* ... */ P.column_n = N.column_N
WHERE P.item_id IS NULL;

or

SELECT N.* 
FROM new_table N 
LEFT JOIN previous_table P 
     ON (P.item_id, P.column_1, P.column_2, /* ... , */ P.column_N) 
       =
        (N.item_id, N.column_1, N.column_2, /* ... , */ N.column_N) 
WHERE P.item_id IS NULL;
0

I think we can use the information_schema for this also. So you dont have to write everything done in a query. My suggestion would be something like that:

select TABLE_NAME,COLUMN_NAME
from information_schema.COLUMNS
where TABLE_NAME='Table_1' and COLUMN_NAME not in(SELECT COLUMN_NAME from 
information_schema.COLUMNS where TABLE_NAME='Table_2')  

Greetings,
Sebastian

0
( SELECT CONCAT_WS("\t", item_id, col1, col2, ...) AS x FROM prev )
UNION ALL
( SELECT CONCAT_WS("\t", item_id, col1, col2, ...) AS x FROM newt )
GROUP BY x
HAVING COUNT(*) = 1;

should give you the rows that are not the same between the tables.

Notes:

  • I assume tab (\t) does not show up in any column.
  • I don't know what will happen with NULLs.
  • It shows both the 'before' and 'after' values, without saying which is which.
  • New rows and delete rows are included in the output.

If item_id is the PRIMARY KEY, then there is a much better way:

SELECT CONCAT_WS("\t", prev.col1, prev.col2, ...) AS "PrevValues",
       CONCAT_WS("\t", newt.col1, newt.col2, ...) AS "NewValues"
    FROM prev
    JOIN newt USING(item_id);

Notes:

  • This won't handle inserts/deletes, however you could get one or the other by using LEFT or RIGHT.

To get the column list typed out for you:

SELECT  GROUP_CONCAT(column_name)
    FROM  information_schema.columns
    WHERE  table_schema = '...'   -- your database name
      AND  table_name = '...';    -- your table name
  • Hi! Thanks for the suggestion. It's a very interesting approach and I certainly wouldn't have thought of that! However, it seems to me that I still have to list out all the columns. That is what I meant by my method being 'untenable if the number of columns is very large'. Hypothetically, if I have a hundred columns, I have to type out hundred columns in my select statement. I'm wondering if there's a way to loop it like a 'for loop': for (int i=0; i<100; ++i) check prev.column[i] != new.column[i] – lardandweed Nov 16 '18 at 13:54
  • @lardandweed - I added code to generate the list of column names (in a string). Then copy the output into my CONCAT_WS (or other code). – Rick James Nov 16 '18 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.