INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

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

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

RE: Help needed with a tricky UPDATE

(OP)
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...

CODE

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.

RE: Help needed with a tricky UPDATE

Perhaps this ?

CODE --> SQL

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

RE: Help needed with a tricky UPDATE

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

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!