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

Update table

Author

Topic

carau2007
Starting Member

31 Posts

Posted - 2012-01-23 : 15:04:54

I have a table with a column file_id which is empty right now. Trying to compare a column in table A with a column in table B and match the primary key to import file_id in table A. I tried to use an insert with an Inner join. But it adds to my table. I want is to match and update the new column by matching crt_number.

Insert into TableA(File_id)
Select TableB.File_id from TableB
INNER JOIN TableA
ON TableA.crt_number = TableB.crt_number

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-23 : 15:18:56

Use UPDATE statement rather than INSERT.

UPDATE TableA SET
   FILE_ID = TableB.FILE_ID
FROM
   TableB
	INNER JOIN TableA
		ON  TableA.crt_number = TableB.crt_number

carau2007
Starting Member

31 Posts

Posted - 2012-01-23 : 15:23:35

Thanks! Works great

carau2007
Starting Member

31 Posts

Posted - 2012-01-23 : 15:55:06

And what if I have to match two column in table B. Will that mean two inner join?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-23 : 15:59:36

Yes.

When I have to do the updates, what I usually do is write a corresponding select to see what will be updated. If I am happy with that, then I would do the update. For example, in the code below, first I would comment the first two lines and uncomment the line that starts with SELECT. That will tell me exactly what the current value in TableA is and what it is going to be updated to.

UPDATE TableA SET
   FILE_ID = TableB.FILE_ID
-- SELECT TableA.TableA,TableB.FILE_ID
FROM
   TableB
	INNER JOIN TableA
		ON  TableA.crt_number = TableB.crt_number

But, to answer your original question, you probably would need to do something like this:

UPDATE TableA SET
   FILE_ID = TableB.FILE_ID
FROM
   TableB
	INNER JOIN TableA
		ON  TableA.crt_number = TableB.crt_number
                    AND TableA.Column2 = TableB.Column2

carau2007
Starting Member

31 Posts

Posted - 2012-01-23 : 16:02:39

Thanks a lot for the great tip and info!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources