Need help with an Update query

Question

Need help with an Update query

Pac123

SSCrazy

Points: 2464
More actions
March 3, 2008 at 10:08 am

#74641

Hello All,
For some reason i canot get the following query to work...
update tf
set ID = pr.attemptID
from prattempts pr, [i]servername[/i].[databasename].[dbo].[tablename] tf
where pr.ID = tf.ID
This is the Logic
There are three tables(abbreviating it so tht i do not give out specific info)
1] CA....The primary Key is ID
2]TF...primary key again ID
So, TF.ID = CA.ID
3]The third table PR.
PR also has ID from the CA table.
I wanto select pr.attemptID into TF, where pr.ID = tf.ID.
But for some reason, it returns no values. Although if i run the query above with the condition that pr.ID = ca.ID it works!! I cannot understand it since Ca.ID = tf.ID.
I hope i made sense, its not really a big deal but for some reason i cannot get past it today!! Any inputs would be welcome
Thanks!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TraderSam SSCrazy Points: 2931 More actions · Answer 1

JSAD (3/3/2008)
update tf
set ID = pr.attemptID
from prattempts pr, [i]servername[/i].[databasename].[dbo].[tablename] tf
where pr.ID = tf.ID
This is the Logic
There are three tables(abbreviating it so tht i do not give out specific info)
1] CA....The primary Key is ID
2]TF...primary key again ID
So, TF.ID = CA.ID
3]The third table PR.
PR also has ID from the CA table.
I wanto select pr.attemptID into TF, where pr.ID = tf.ID.
But for some reason, it returns no values. Although if i run the query above with the condition that pr.ID = ca.ID it works!! I cannot understand it since Ca.ID = tf.ID.
I hope i made sense, its not really a big deal but for some reason i cannot get past it today!! Any inputs would be welcome
Thanks!!

So you want to update tf.ID to be pr.attemptID where currently, tf.ID = pr.ID? Is this correct?

If it was easy, everybody would be doing it!;)

Pac123 SSCrazy Points: 2464 More actions · Answer 2

No, sorry my mistake

I wanto insert values into TF.transID where tf.ID = pr.ID.

The transID comes from the pr table[thus tf.transID = pr.attemptID]

Thanks

TraderSam SSCrazy Points: 2931 More actions · Answer 3

JSAD (3/3/2008)
I wanto insert values into TF.transID where tf.ID = pr.ID.
The transID comes from the pr table[thus tf.transID = pr.attemptID]

update tf

set tf.transID = pr.attemptID

from prattempts pr, [servername].[databasename].[dbo].[tablename] tf

where pr.ID = tf.ID

This will update tf.tranID to be pr.attemptID where pr.ID = tf.ID.

If it was easy, everybody would be doing it!;)

Pac123 SSCrazy Points: 2464 More actions · Answer 4

I tried that, it still didnt update any rows, this is puzzling, because if i try this query

select top100 * from process pr,caadv ca

where ca.caID= pr.caID

It positively returns rowsets.

the TF table has its ID = ca.caID i.e. [ca.caID = tf.ID]

So as you suggested its probably a problem in the way im structuring the query, but im not sure what

Thanks for your suggestion 🙂

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 5

Ya know... you'd get a lot better help quicker if you did what the URL in my signature lines tells you to do... just a thought... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)