Try to link two table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Try to link two table

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-14 : 20:52:55

Hi guys,

I am in trouble need help, I have two table
TABLE A and Table B
Table A has
ID,FNAME,R_ID
1,MIKE,25
2,JAMES,45
1,SMITH,26

TABLE B has
ID,CITY,R_ID
1,CA,NULL
2,MA,NULL
1,NY,NULL

I want to Populate Table B.R_ID from Table A.R_ID i am using update statement

UPDATE TABLE B
SET R_ID = TABLE A.R_ID
FROM TABLE B
INNER JOIN TABLE A
ON TABLE A.ID = TABLE B.ID

Here is the result i am getting
TABLE B
ID,CITY,R_ID
1,CA,25
2,MA,45
1,NY,25

I want result like this
TABLE B
ID,CITY,R_ID
1,CA,25
2,MA,45
1,NY,26

SO i tried everything to fix this issue but i couldn't achieve, here i have solution but i need your help.
I have to add one column in Table A and Add one more column in Table B

but my question is how i give a value to TABLE A.ID, 1 SAME to TABLE B.ID ?

so then i can use this update statement.

UPDATE TABLE B
SET R_ID = TABLE A.R_ID
FROM TABLE B
INNER JOIN TABLE A
ON TABLE A.ID = TABLE B.ID
and TABLE A.TEST = TABLE B.TEST

If someone know better way to do this please feel free to let me know. If any one need more info or my question is not clear i can give you more information. Thanks.

lappin
Posting Yak Master

182 Posts

Posted - 2011-04-15 : 11:19:36

Your code looks right to me. I use this as general template:
UPDATE t2
SET Fld2 = t1.Fld2
FROM Table2 t2
INNER JOIN Table1 t1
ON t2.Fld1 = t1.Fld1

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-15 : 12:16:05

How do you match
1,MIKE,25 to 1,CA,NULL and not 1,NY,NULL

Doesn't look to be anything here to join on (which is the problem you are finding).
What you are requesting can't be done with your current data.

You can take the IDs from both tables and when they are the same match on the order of the name and city (alphabetically) but I suspect that's not what you want.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-18 : 19:08:55

Table A has
ID,FNAME,R_ID
1,MIKE,25
2,JAMES,45
1,SMITH,26

TABLE B has
ID,CITY,R_ID
1,CA,NULL
2,MA,NULL
1,NY,NULL

what i am trying to do to populate TABLE B.R_ID FROM TABLE A.R_ID and ID is the link.
Code is fine no problem but the problem is duplicate ID, that's why they giving me Duplicate R_ID. Please let me know if you need more explanation. Thanks.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-18 : 19:26:09

sounds more like homework than real life. either way the only way you can do it is manual because as nigelrivett there is nothing to join on.

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources