Update using join of 2 tables

[Agro42]

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.

 

[blom0344]

look here:

http://www.tek-tips.com/viewthread.cfm?SQID=809071&SPID=178&page=1

I think your syntax is basically wrong. Avoid using the inner join syntax and use the where clause for joins if you do not have any outer joins. This will make the SQL more compact

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Agro42]

The INNER JOIN clause is something I've never used before. I was taught to join my tables in the WHERE clause but all the stuff I found searching the web said I needed an inner join.
My original update looked like this:

update aprf3951 a set (a.passfail, a.testdate) = (select 'F', b.admdate from atpa3951 b where a.stulink = b.stulink and a.testlink = b.partnum and b.testlink in (61, 62, 63)) where a.stulink = b.stulink and a.testlink = b.partnum and b.testlink in (61, 62, 63)

and my errors were mainly either unexpected tokens or "SQL0206N "B.STULINK" is not valid in the context where it is used.
SQLSTATE=42703"

 

[MarcLodge]

I would go with your original method. The problem you have is that you are referring to an inner table in the outer query. To see what I mean, it's best to format your update such:
UPDATE APRF3951 A
SET (A.PASSFAIL, A.TESTDATE) =
(SELECT 'F',
B.ADMDATE
FROM ATPA3951 B
WHERE A.STULINK = B.STULINK
AND A.TESTLINK = B.PARTNUM
AND B.TESTLINK IN (61, 62, 63))


WHERE A.STULINK = B.STULINK
AND A.TESTLINK = B.PARTNUM
AND B.TESTLINK IN (61, 62, 63)

From this, you can see that in the portion in italics you are attempting to reference the 'B' table in the outer query, where it does not exist. You need to get rid of it, and replace it with the selection criteria (if any exists) that is relevant to the 'A' table on it's own.

Hope this helps.

Marc

 

[Agro42]

I'm getting closer.
This code

UPDATE APRF3951 SET (passfail, testdate) = (Select Distinct 'F', max(ATPA3951.admdate) From ATPA3951 Where APRF3951.stulink = ATPA3951.stulink AND APRF3951.testlink = ATPA3951.partnum AND ATPA3951.testlink in (61, 62, 63) And (ATPA3951.scoreone like '%N%' or ATPA3951.scoreone like '%F%') And Exists (Select 1 From ATPA3951 Where APRF3951.stulink = ATPA3951.stulink AND APRF3951.testlink = ATPA3951.partnum AND ATPA3951.testlink in (61, 62, 63) And (ATPA3951.scoreone like '%N%' or ATPA3951.scoreone like '%F%')))

populates the date correctly, but puts an 'F' in every record, not just the ones where the sudent failed the test. I know I'm missing either a WHERE clause or possibly a subselect but every time I try to put one in, I hit the inner/outer problem that Marc pointed out.

 

[MarcLodge]

Ag,
What are you expecting, apart from 'F', to go in the passfail field? From your query, you are only specifying 'F'.

The update statement can be based on a select statement, but when hard coding literals, that's what you are gong to get every time.

If you break your query down into it's basest form, you could be left with something like:
UPDATE APRF3951 SET (PASSFAIL, TESTDATE) =
(SELECT 'F', CURRENT DATE
FROM ATPA3951 B
WHERE A.STULINK = B.STULINK)

Now obviously this is NOT your query, but should illustrate where you are going wrong. In the query above, it would insert F and current date for every row found on ATPA3951. Like your query, it offers no alternative value for PASSFAIL.

I hope this helps. Please get back to us with some more info, and we will be able to help out further (hopefully!).

Marc

 

[Agro42]

Marc, I was expecting the 'F' to be set only where there was a matching fail record on the ATPA table, and left with the already set ' ' when there was no fail record. My problem was that I had to compare both stulink AND testlink for each record, which messed me up when I used IN (select...) statements.
I got around the problem by using type casting and concatenation. I have a where clause that contains

where curr_sch = '951' and char(curr_link) || char(decimal(3,2,0)) not in (select char(stulink) || char(testlink) from aprf3951)

The decimal cast is because the testlink field is defined as decimal(2,0) even though it only ever contains smallint values.
I eventually ran this as a series of inserts rather than 1 insert then a series of updates, but the key is still the concatenation.
Thanks to Marc and Mr Blom for your suggestions. You helped me work through the problem.