Need help with an Update query
-
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!!
-
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!;)
-
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
-
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!;)
-
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
-
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)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply