Help needed with a tricky UPDATE 1

[VickyC]

greetings

I'm asking for help in writing an UPDATE query. As I'll explain below, the UPDATE rule is a bit peculiar.

Consider the following table. Val1 runs from 1 to 8. For each Val1, Row runs from 1 to 14.
So, there are 14 x 8 = 112 records.

For exactly half (56) of these records, Val2 = 0, and for the other half of the records, Val2 > 0.

tblTEST

Row	Val1	Val2
1	1	3
2	1	4
3	1	5
4	1	0
5	1	0
6	1	8
7	1	7
8	1	6
9	1	0
10	1	0
11	1	2
12	1	0
13	1	0
14	1	0
1	2	6
2	2	7
3	2	8
4	2	0
5	2	0
6	2	5
7	2	4
8	2	0
9	2	0
10	2	3
11	2	0
12	2	0
13	2	0
14	2	1
1	3	0
2	3	0
3	3	0
4	3	6
5	3	8
6	3	7
7	3	0
8	3	0
9	3	4
10	3	0
11	3	0
12	3	1
13	3	2
14	3	5
		
......  etc  .....
		
11	8	0
12	8	5
13	8	4
14	8	6

The first 2 columns are inputs that determine the value in the 3rd column.

So, (Row, Val1) --> Val2. For example, (3, 1) --> 5


Here's the goal: I want to UPDATTE all 56 of the 0's in Val2 according to the following rule...

When (r, p) --> q, WHERE q > 0, then we UPDATE (r, q) --> -p. In all cases, the value about to be UPDATEd will be a 0. (The table is already set up that way.) After the UPDATE, it will be a negative number.

Here's two examples...

a) (1, 1) --> 3, which is > 0, so we UPDATE the 0 in column Val2 so that (1, 3) --> -1

b) (13, 3) --> 2, which is > 0, so we UPDATE the 0 in column Val2 so that (13, 2) --> -3



Thanks in advance for any clues.
Vicky C.

 

[PHV]

Something like this ?
UPDATE tblTEST A
SET Val2=-(SELECT B.Val1 FROM tblTEST B WHERE B.Row=A.Row AND B.Val1=A.Val2)
WHERE Val2=0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

hi PHV - thanks for responding. Your suggestion looks really close. but I have a few comments...

1. I think the last part of the subquery's WHERE atatement should be B.Val2 = A.Val1, not B.Val1 = A.Val2

That would make the code like this...

UPDATE 	tblTEST2 A
SET	A.Val2 = -(
SELECT 	B.Val1
FROM	tblTEST2 B
WHERE	(B.Row = A.Row) AND  (B.Val2 = A.Val1)
	)
WHERE 	A.Val2 = 0;

2. But, I still get the error message "Operation must use an updateable query".

I've been researching causes for this error, but I just can't get the code to run. When I go through the SQL 'by hand', it looks like it should work???

any suggestions would be really appreciated.

Vicky C.

 

[PHV]

Perhaps this ?

UPDATE 	tblTEST2
SET	Val2 = -DLookUp('Val1','tblTEST2','Row=' & Row & ' AND Val2=' & Val1)
WHERE 	Val2 = 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

hi PHV - I just tweaked your first attempt a bit to give the following...

UPDATE
	tblTEST A
INNER JOIN
	tblTEST B
ON
	(A.Row = B.Row) AND (A.Val1 = B.Val2)
SET
	A.Val2 = -1*(B.Val1)
WHERE
	A.Val2 = 0;

This works perfectly. Thanks for the major hint, and for your 2nd solution as well!

Vicky C.