December 8, 2010 at 7:04 pm
I have
Table A
ID LNAME RID
1 Mike A
2 Jim B
1 Smith C
4 Larry D
Table B
ID State L_RID
1 CA G
2 GA F
3 FL H
9 NY G
1 VA I
I want to update Table B, and i m using this update statement
UPDATE TABLE B
SET TABLE B. L_RID = TABLE A. RID
INNER JOIN TABLE A
ON TABLE A. ID = TABLE A.ID
and i am getting this result
ID State L_RID
1 CA A
2 GA B
3 FL NULL
9 NY G
1 VA A
That's not i want,
I want like this
ID State L_RID
1 CA A
2 GA B
3 FL NULL
9 NY G
1 VA C
Please guide me where i am wrong and how i can address this issue. Thanks.
December 8, 2010 at 9:03 pm
It's because ID one is repeated in your connection. What Update will do is update each row with each value, so first it sets it to the first instance of ID 1, then it sets both to the second instance of ID 1... and so on.
You need a one to one match to do this properly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 8, 2010 at 9:08 pm
Thanks for your reply, How i can do one to one match and update in sql syntax ? Could you please post sql syntax for me?
December 8, 2010 at 9:14 pm
rocky_498 (12/8/2010)
Thanks for your reply, How i can do one to one match and update in sql syntax ? Could you please post sql syntax for me?
With that data, you can't, not directly. You need to add in a column or some additional method of attaching the specific rows together. You could probably fake one with a row_number() OVER ( ORDER BY ID) on both tables and then connect on both the row_number AND the ID... but that's a pretty random way of hooking the data together intelligently.
You need to dig into the data and either manually add direct 1 to 1 connectors or find another join association set of columns.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 8, 2010 at 10:17 pm
rocky_498, what other columns are in your tables? Post your table DDL, PK definitions, indexes, and sample data. See the article in my signature line for help with posting those.
December 9, 2010 at 6:02 am
Provide the complete table structure and your requirement clearly ...
Are you looking for something like below.(not sure) :unsure:
Update B
Set B.L_RID=A.RID
from TableB B inner join TableA A on B.ID=A.ID
December 9, 2010 at 6:14 am
Can you tell me How are you getting
3 FL NULL
9 NY G in your result set
December 12, 2010 at 9:46 pm
I am using update syntax
UPDATE TABLE B
SET TABLE B. L_RID = TABLE A. RID
INNER JOIN TABLE A
ON TABLE A. ID = TABLE A.ID
December 12, 2010 at 9:50 pm
UPDATE TABLE B
SET TABLE B. L_RID = TABLE A. RID
INNER JOIN TABLE A
ON TABLE A. ID = TABLE B.ID
I want if Table A.ID Match with Table B.ID Update Table B. L_RID. Please let me know if you want more explanation.
December 13, 2010 at 8:28 am
jscot91,
Is this a new question from a new poster or did you change user names?
If this is a new question from a new poster, please post a new thread in the forum. Based on the vagueness of your question, please also include a detailed description of what it is you are trying to do and the results you want. Include table DDL, sample data, and an example of the final results. Please refer to the article in my signature line for guidence in creating your new thread.
If this is the same original poster, please do as we've asked and provide us with the additional information that we've asked for. Use the article in my signature line for guidence.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply