Copy column values from on tbl to another col tbl

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)

Copy column values from on tbl to another col tbl

Author

Topic

MrCapuchino
Starting Member

9 Posts

Posted - 2010-07-16 : 23:46:57

Hello,

I have 2 tables

Table 1

name | lastname
jim johnson
john johnson

table 2

id | user | othername
1 johny johnson
2 johny stewart

I want to copy the values from table1.lastname into table2.othername

I tried this

UPDATE table2 set othername = (select lastname from table1) where name = 'john'

I get an error saying it cannot copy more than one value.

Any ideas?
Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-17 : 00:45:17

I think this is what you're trying to do

UPDATE	t2
SET	othername = t1.lastname
FROM	Table1 t1
JOIN	Table2 t2
ON	t2.user = t1.name
WHERE	t1.name = 'john'

but you're going to trash table 2 if there is more than one 'john' in table 1

MrCapuchino
Starting Member

9 Posts

Posted - 2010-07-17 : 09:06:50

Hello,

Could you help me understand the query?

I mean t2.user = t1.name if you look closely there is no equal value in those 2 columns.
But let me give you a more clear example of my table.
TABLE1
Categories | Division
CAT1
CAT2
CAT3
CAT4
CAT5

Username | Categories2
JOHN
JOHN
JOHN
JOHN
JOHN

so Basically, I wanto to copy the categorios column from TABLE1 to the Objectives column in TABLE2 where the username is 'JOHN' resulting in

TABLE2
Username | Categories2
JOHN CAT1
JOHN CAT2
JOHN CAT3
JOHN CAT4
JOHN CAT5

THANKS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources