displaying records that could not be updated mysql

Question

Hi,

I am trying to update my results table with matching values from another table. While, updating , is there any possibility to display records that have not been updated(not matched)?

BEGIN WORK
UPDATE results, testcases
SET    results.testset = testcases.TestSet
WHERE  results.TestCase = testcases.TestCase

Answer 1

The immediate answer is to select all whose WHERE clause is met, but the SET result is not:

SELECT * 
FROM results, testcases
WHERE results.TestCase = testcases.TestCase
  AND resutls.testset <> testcases.TestSet

Answer 2

I'm assuming the reason you want this while updating is to avoid incorrect information due to concurrent updates. If that's not the case, just use the query shown.

There is no way in a single query. So you'd need to use transaction boundaries, and query hints to hold onto locks to perform an existence query to find rows that are not matched.

SELECT  *
FROM    Results r
WHERE   NOT EXISTS (
        SELECT  *
        FROM    TestCases t
        WHERE   r.TestCase = t.TestCase
        )