select and update at same time

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 2000 Forums

SQL Server Development (2000)

select and update at same time

Author

Topic

rjrichar40
Starting Member

2 Posts

Posted - 2006-04-20 : 17:47:32

I've dealt a lot with basic SQL stuff, but never tried this before. I'm sure its not hard for some of you!

I want to "map" the values from one column in Table A to a different column in Table B. The two tables are joined by common IDs. I could probably write a vbscript to do this, but figured there is a T-sql way to handle it.

Is there a way to say something like "SELECT * FROM TABLEA" and at the same time loop through each record and do an "UPDATE TABLEB SET (tableB.columnX.value = tableA.columnY.ThisValue) WHERE TABLEB.TABLEBid = tableA.TableAid.value" for each row of the table?

Any ideas?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-20 : 18:07:36

You can do this with one update statement. No reason to loop. This should get you started:

UPDATE b
SET ColumnX = a.ColumnY, ...
FROM TableB b
INNER JOIN TableA a
ON b.TableBID = a.TableAID

Tara Kizer
aka tduggan

rjrichar40
Starting Member

2 Posts

Posted - 2006-04-21 : 10:18:54

Excellent! That did exactly what I needed. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources