Stuck with SQL Update statement, possible Inner Join (Unsure)?

Question

I have a MS Access table with 3 columns named Code, [Money Amt], and Line in a table named Actual Debt.

I am trying to write an SQL statement that will help me automate this process. I want the statement to look for rows with identical values in their code columns. Then look at the [Money Amt] and locate pairs that have the reverse values (In this case the pair would be 200, and -200). When it finds the pair of rows with the same Code value and reverse [Money Amt] it will then populate the Line column value with 999.

In this case the second and fourth row would be selected as the pair and have the resulting line value of 999.

I know that this will probably require an INNER JOIN. I haven't gotten very far besides this, I don't know how to structure the INNER JOIN due to being a newbie.

UPDATE [Actual Debt] SET Line = 999;

Possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Gilad Green, Jul 13 '16 at 15:50
Exact duplicate of http://stackoverflow.com/questions/38329845/sql-in-ms-access-trouble-with-inner-join/38330336#38330336. You should reword your original question as it's on hold - this has just added some formatting. For what it's worth - I'm not sure why it was put on hold in the first place, but my response to your original question answered it (so I thought). — Darren Bartrup-Cook, Jul 13 '16 at 16:20
`UPDATE [Actual Debt] T3 INNER JOIN [Actual Debt] T4 ON T3.[Money Amt] = -T4.[Money Amt] AND T3.Code <> T4.Code SET T3.Line = 999` - from original post. — Darren Bartrup-Cook, Jul 13 '16 at 16:25

score 1 · Answer 1 · answered Jul 13 '16 at 15:50

1

I'm not sure about MS Access syntax, but this is the general idea.

update t1
set line = 999
from [actual debt] t1 join [actual debt] t2 on t1.code = t2.code
and t1.[money amt] = t2.[money amt] * -1

answered Jul 13 '16 at 15:50

Dan Bracuk

20,699
4
26
43

I recognize this syntax as SQL Server syntax, but not MS Access syntax. – Gordon Linoff Jul 13 '16 at 15:52

score 1 · Accepted Answer · answered Jul 13 '16 at 15:51

1

You are on track. Here is one way:

update [Actual Debt]
    set line = 999
    where exists (select 1
                  from [Actual Debt] as ad2
                  where ad2.code = [Actual Debt].code and
                        ad2.[Money Amt] = - [Actual Debt].[Money Amt]
                 );

answered Jul 13 '16 at 15:51

Gordon Linoff

1,242,037
58
646
786

This worked flawlessly, and I now understand it better. Thank you! – Mccormack Jul 13 '16 at 16:24
Actually now that I analyze the database further I am noticing that there a few instances where there are 3 lines with the 999 code that have the same code and [Money Amt]. There should only be pairs, so their should be a 1:1 ratio with a code that has an opposing money amt of the same code. I can tell it would have to do with the subquery, but I'm not sure how to tell it to do pairs only. Is there a way to fix this? Sorry if this is confusing – Mccormack Jul 13 '16 at 17:43
@Mccormack . . . Can you ask another question with appropriate sample data and desired results? – Gordon Linoff Jul 14 '16 at 02:12

Stuck with SQL Update statement, possible Inner Join (Unsure)?

2 Answers2