Duplicating Column data in same table

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)

Duplicating Column data in same table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-06 : 07:55:03

Henry writes "OS = Windows 2000 Server
SQL Server Version = SQL 2000 SP4

I have a table, iciwhs, with fields citemno, clocation, cwarehouse, etc.
There are 5 cwarehouse values each with the same citemno values.
Only one cwarehouse has clocation values.
I need to make each cwarehouse have the same clocation values.

As a relative novice with SQL scripts I thought I could use the UPDATE SET. However it seems that UPDATE SET assumes you have two separate tables. I did see something about using a subquery but could not get it to work either.
Any help would be greatly appreciated."

mriverol
Starting Member

10 Posts

Posted - 2005-10-07 : 07:29:57

Henry, a little confused about what it is you are trying to do. I think you have a table that look similar to this and you want to populate te null cLocation columns.

cItemNo cLocation cWarehouse
1 LocationA warehouse1
1 NULL warehouse1
1 NULL warehouse1
1 NULL warehouse1
1 NULL warehouse1
2 LocationB warehouse2
2 NULL warehouse2
2 NULL warehouse2
2 NULL warehouse2
2 NULL warehouse2

If this is the case then the followinf update query will work

update a
set clocation = b.clocation
from iciwhs a
inner join (
select
max(clocation) clocation,
citemno
from iciwhs
group by
citemno,
cwarehouse
) b
on a.citemno = b.citemno
where a.cLocation is null

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources