INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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

(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

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close