displaying records that could not be updated mysql
I am trying to update my results table with matchin开发者_开发问答g 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
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
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
)
精彩评论