Update Help! - 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)

Update Help!

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2010-10-31 : 12:22:11

Table A:-
ID(P.K),ADD,STREET#

Table B:-
ID(P.K),LNAME,FNAME,ADD,L_ID(link to Table A)

QUESTION:- How i can Link Table B to Table A.
I want Table B.L_ID = Table A.ID. Table A and Table B is already created and has data.

Please reply. Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 12:31:28

Does one or more of the columns in tableb hold data from tablea?
add and fname matching add and street# for instance

update tableb
set i_id = a.id
from tableb b
join tablea a
on a.add = b.add
and a.street# = b.fname

If not then I doubt if it can be done.

==========================================
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 - 2010-10-31 : 14:23:37

Thanks for your reply, I forgot to mention, there is one more field that matched both table
Table A.Snumber
Table B.Snumber

Sonu619
Posting Yak Master

202 Posts

Posted - 2010-10-31 : 14:27:22

One more thing,
Table A.Snumber = 12 34 6
Table B.Snumber = 12346

how i can match this one with "add" Thanks in advance.

Sonu619
Posting Yak Master

202 Posts

Posted - 2010-10-31 : 14:42:24

Update Table B
set I_id = tableA.id
from tableB
join tableB
on tableA.snumber = tableB.snumber
and tableA.add = tableB.add

"Query Success 0 row infected"

Please guide me where i am wrong.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 15:38:44

Which columns match on tableA and tableB
that is saying that the add,snumber comination in TableA never appears in TableB hence no updates.

try choosing 1 row from tableb - how do you know which row to use from tablea to update it?

==========================================
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 - 2010-10-31 : 16:10:06

i m using this syntax

UPDATE B
SET B.L_ID = A.ID
FROM TableB B
INNER JOIN TableA A
ON A.Snumber = REPLACE(B.Snumber, ' ', '')
AND A.add = B.add

* i m getting error "A.Snumber can't be bound"
Any advice?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 16:41:38

Probably means there isn't a SNUMBER on TableA

try it with a select first
select top 1 *
from TableB B
INNER JOIN TableA A
ON A.Snumber = REPLACE(B.Snumber, ' ', '')
AND A.add = B.add

You could post the relevent columns in a row from tablea and tableb that you expect to be used in the update.

==========================================
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 - 2010-10-31 : 17:01:13

Create Table A (
ID Varchar(15),
ADDR VARCHAR(20),
STREETNO VARCHAR(25)
)

Create Table B(
ID Varchar(15),
LName Varchar(20),
FName Varchar(20),
ADDR Varchar(20),
L_ID Varchar(15)
)

-- Insert values in Table A
Insert into A Values ('1','abc','2')
Insert into A Values ('2','xyz','5')
Insert into A Values ('3','abc','9')

--Insert values in Table B
Insert into B Values ('12','Frank','Smith','2','1')
Insert into B Values ('23','Frank','Smith','5','1')
Insert into B Values ('32','Frank','Smith','9','1')

Note:- I want to link Table B with Table A,
Table A = ID
Table B = L_ID

I want same value Table A "id" has in Table B "L_ID". Please feel free to let me know if need more info. Thanks for your help.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 17:16:40

The only thing you seem to have there is the StreetNo --> Addr which doesn't sound good

update B
from B
join A
on B.ADDR = A.STREETNO

I would check that this is correct first - try a select to get the first 50 rows that are to be updated.
I very much doubt that STREETNO is unique in A.

==========================================
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 - 2010-10-31 : 17:26:43

I am sorry ID is same on both table

TableA = ID = 1,2,3
TableB = ID = 1,2,3

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 17:45:29

I doubt it seeing as your original request was to update I_ID on B to be ID on A.
You already have the value in ID on B so no need for the update.

==========================================
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 - 2010-10-31 : 18:11:11

No no I want to update "TableB.L_ID"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 20:02:18

Then I'll repeat the earlier question
Which columns match on tableA and tableB

If A.ID = B.ID
then your update is just
update B
set I_ID = ID

and I'm pretty sure that's not what you want.
Think you need to post some sample data.

==========================================
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 - 2010-10-31 : 23:40:00

Is it possible if you provide me your e.mail so i can send you table structure and document to explain. For security reason i can't issue table structure right here. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources