Can this type of update be done? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Can this type of update be done?

Author

Topic

Pat Shaw
Starting Member

14 Posts

Posted - 2006-03-16 : 14:35:07

I have 2 tables, Table A & Table B. They both have an identical structure with exactly the same column names.

I need to perform an update to Table A using the values in table B where the data in one column matches, but I would like to do this as a sort of bulk update instead of specifying column names (if this is possible). Here is an example:

Update A
SET A* = (SELECT * FROM B)
WHERE A.xyz = B.xyz

I hope this makes some sort of sense. If not please say and I will attempt to phrase it a bit clearer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-16 : 14:37:42

You'll have to explicitly write out the columns in the SET.

UPDATE a
SET Column1 = b.Column1, Column2 = b.Column2
FROM TableA a
INNER JOIN TableB b
ON a.xyz = b.xyz

Tara Kizer
aka tduggan

Pat Shaw
Starting Member

14 Posts

Posted - 2006-03-16 : 14:46:38

So there is no way to do an update without specifying the column names? This is really what I wanted to avoid as there are alot of columns.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-16 : 14:47:53

That's correct.

Tara Kizer
aka tduggan

Pat Shaw
Starting Member

14 Posts

Posted - 2006-03-16 : 14:49:25

Thanks for your time.

pootle_flump

1064 Posts

Posted - 2006-03-17 : 07:10:15

If you are updating ALL columns you could delete matching rows from Table A and then insert


DELETE FROM TableA
WHERE EXISTS(SELECT * FROM TableB WHERE TableA.xyz = TableB.xyz)

INSERT INTO TableA
SELECT * FROM TableB

Or you could modify this script that lists all table column names:

DECLARE @ObjName as VarChar(100)
DECLARE @ColList AS VarChar(2000)

SELECT @ObjName = 'MyTableName'

SELECT @ColList = ISNULL(@ColList, '') + Column_Name + ', ' 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE Table_Name = @ObjName

SELECT @ColList = SUBSTRING(@ColList, 1, LEN(@ColList)-1) 

PRINT 'SELECT ' + @ColList + ' FROM ' + @ObjName

HTH

pootle_flump

1064 Posts

Posted - 2006-03-17 : 08:34:08

I was bored...


DECLARE @ObjName as VarChar(100)
DECLARE @ObjSourceName as VarChar(100)
DECLARE @ColList AS VarChar(2000)

SELECT @ObjName = 'TableA', @ObjSourceName = 'TableB'

SELECT @ColList = ISNULL(@ColList, 'UPDATE ' + CHAR(9) + @ObjName + CHAR(10) + 'SET ' + CHAR(9)) + Column_Name + ' = ' + Column_Name + ', ' + CHAR(10) + CHAR(9)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE Table_Name = @ObjName

SELECT @ColList = SUBSTRING(@ColList, 1, LEN(@ColList)-4) + CHAR(10) + 'FROM ' + CHAR(9) + @ObjName + CHAR(10) + CHAR(9) + CHAR(9) + ' INNER JOIN '  + @ObjSourceName

PRINT @ColList

jhermiz

3564 Posts

Posted - 2006-03-17 : 08:55:41

I think listing out each column name is much simplier than that.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

pootle_flump

1064 Posts

Posted - 2006-03-17 : 09:24:22

quote:
Originally posted by jhermiz

I think listing out each column name is much simplier than that.

Oh yes - much simpler. But I presume if he requires it once he will require it again for another set of tables. It can't get much simpler than hitting F5.

I agree that (certainly for a single run) it probably isn't worth the effort of a script but he seemed keen to avoid typing it all out. SQL Prompt would be another means of avoiding all those nasty finger movements.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources