## Help needed with a tricky UPDATE

## Help needed with a tricky UPDATE

(OP)

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

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...

Here's two examples...

a)

b)

Thanks in advance for any clues.

Vicky C.

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

#### CODE

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.

## RE: Help needed with a tricky UPDATE

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: How Do I Get Great Answers To my Tek-Tips Questions?

FAQ181-2886: How can I maximize my chances of getting an answer?

## RE: Help needed with a tricky UPDATE

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...

## CODE

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.

## RE: Help needed with a tricky UPDATE

## CODE --> SQL

Hope This Helps, PH.

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?

FAQ181-2886: How can I maximize my chances of getting an answer?

## RE: Help needed with a tricky UPDATE

## CODE

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

Vicky C.