I need to update 1 table from another table. Simple enough, but I can't get it to work.
Here are my tables and the important fields:
APRF
stulink
testlink
passfail
testdate
ATPA
stulink
testlink (not the same as APRF.testlink)
partnum (IS the same as APRF.testlink)
scoreone
admdate
There is no referential integrity turned on.
ATPA contains test scores for multiple types of tests, of which I am only interested in ATPA.testlink in (61, 62, 63).
APRF contains 3 rows for every student, 1 each of APRF.testlink in (1, 2, 3) which correspond to ATPA.partnum
My current query (which doesn't work) is:
update atpa3951 a inner join aprf3951 b on (a.stulink = b.stulink and b.testlink = a.partnum) set passfail = 'F', testdate = (select admdate from atpa3951 g inner join aprf3951 h on g.stulink = h.stulink and h.testlink = g.partnum where h.testlink in (61, 62, 63) and (g.scoreone like '%N%' or g.scoreone like '%F%')) where b.testlink in (61, 62, 63) and (a.scoreone like '%N%' or a.scoreone like '%F%'))
and the error is:
SQL0104N An unexpected token "update atpa3951 a" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<joined_table>".
SQLSTATE=42601
Any help would be greatly appreciated.
Here are my tables and the important fields:
APRF
stulink
testlink
passfail
testdate
ATPA
stulink
testlink (not the same as APRF.testlink)
partnum (IS the same as APRF.testlink)
scoreone
admdate
There is no referential integrity turned on.
ATPA contains test scores for multiple types of tests, of which I am only interested in ATPA.testlink in (61, 62, 63).
APRF contains 3 rows for every student, 1 each of APRF.testlink in (1, 2, 3) which correspond to ATPA.partnum
My current query (which doesn't work) is:
update atpa3951 a inner join aprf3951 b on (a.stulink = b.stulink and b.testlink = a.partnum) set passfail = 'F', testdate = (select admdate from atpa3951 g inner join aprf3951 h on g.stulink = h.stulink and h.testlink = g.partnum where h.testlink in (61, 62, 63) and (g.scoreone like '%N%' or g.scoreone like '%F%')) where b.testlink in (61, 62, 63) and (a.scoreone like '%N%' or a.scoreone like '%F%'))
and the error is:
SQL0104N An unexpected token "update atpa3951 a" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<joined_table>".
SQLSTATE=42601
Any help would be greatly appreciated.