0

I have the following table called 'RESULT' that I get values for, from another table called 'Temp_main'

The fields in the 'RESULT' are like the following:

StudentName | SujectName | Result
---------------------------------
Adam        | Math       | Fail
Bob         | History    | Pass
Catherine   | Math       | Pass
Dave        | Science    | Fail
Evan        | History    | Pass

Primary key here is (StudentName,SubjectName)

I use the following code to get the data into the table:

insert into result(studentName,subjectName,result)
select StudentName,SubjectName,result
from temp_main

The temp_main table does not have the same primary key, so there are duplicate key values when I insert the data into result table.

Assume that a student appears for a test on the same subject more than once. All his attempts are on the temp_main table, while only his best result is on the Result table.

I am trying to get a ON DUPLICATE KEY UPDATE code that updates the result of a student-subject pair if and only if the latest result is 'Pass'

I tried

    insert into result(studentName,subjectName,result)
    select StudentName,SubjectName,result
    from temp_main
on duplicate key update result = case when temp_main(result) = 'Pass'
Then result(result)='Pass'

I am sure the this is crappy code. But I could not get any better solution.

Kumaran Senapathy
  • 1,233
  • 4
  • 18
  • 30
  • 'latest result'? Is there a `resultDate` column on the temp_main column that you are not telling us about? – Tom Mac Apr 08 '13 at 15:11
  • No. I meant the result on the temp_main column. Sorry for the confusion. – Kumaran Senapathy Apr 08 '13 at 15:25
  • @KumaranSenapathy - What Tom is referring to is that SQL doesn't really maintain things in 'order'. The **ONLY** way to ensure a specific 'latest' (or whatever) row is to use an `ORDER BY` clause with the relevant column. If you don't have such a column, you're basically toast, because all rows are identically ordered. – Clockwork-Muse Apr 08 '13 at 15:49

1 Answers1

1

You are missing an END from you SQL

INSERT INTO result(studentName,subjectName,result)
SELECT StudentName,SubjectName,result
FROM temp_main
ON DUPLICATE KEY UPDATE result.result = CASE WHEN result.result = 'Pass' THEN 'Pass' ELSE VALUES(result.result) END
Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • I dont get any errors, but update did not quite happen. It still reflects 'Fail'. – Kumaran Senapathy Apr 08 '13 at 15:34
  • I have set your tables up on my machine and had a play. There is one issue as MySQL seems to get confused having a column called result in both the table you are inserting to and the source table, and you can't apply an alias to the table name you are inserting to. As a fix to prove it worked I changed the name of the result column on temp_main and it worked fine. – Kickstart Apr 08 '13 at 15:54
  • I used the table names before 'results'. See edit. That still did not help – Kumaran Senapathy Apr 08 '13 at 16:33
  • I have tested it with the table names specified and to me it appears to work fine. If an existing fail has a matching pass on temp_main then it is updated to a pass, while if an existing pass has a matching fail on temp_main then it is not updated. Note that I have updated the SQL to specify the tables, and these are NOT the ones used in the edit you suggested. – Kickstart Apr 09 '13 at 08:29
  • Is there an other way to specify tables in the SQL other than, "table_name.column_name" ? – Kumaran Senapathy Apr 09 '13 at 13:17
  • In general, not that I know of (there are exceptions, such as using the column number when specifying a sort) – Kickstart Apr 09 '13 at 13:21
  • CASE WHEN result.result = 'Pass' THEN 'Pass' ELSE VALUES(result.result) END I do not understand why you are checking the condition of result.result.. is it not temp_main table that should be checked?? – Kumaran Senapathy Apr 09 '13 at 13:23
  • It is checked as if it is already set to pass then whatever it on the temp table will be ignored. Ie, if the options are pass or fail and results stores the best result, then when a pass is stored then the result cannot be better. – Kickstart Apr 09 '13 at 13:27
  • But, if it is a 'fail' on the result table, by the SQL query , the value is not changing. As you can see, there is no value that is taken from the temp_main table in the UPDATE query. ON DUPLICATE KEY UPDATE "result.result" = CASE WHEN "result.result" = 'Pass' THEN 'Pass' ELSE VALUES("result.result") END all the tables are the same.. I am still confused. Please advice. – Kumaran Senapathy Apr 09 '13 at 13:33
  • No, if the result table is holding 'fail' then the result is set to "VALUES(result.result)". VALUES mean it is using the value that was being assigned into that field in the INSERT. – Kickstart Apr 09 '13 at 13:36