Copying column data to another column

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

General SQL Server Forums

New to SQL Server Programming

Copying column data to another column

Author

Topic

aniko
Starting Member

25 Posts

Posted - 2010-12-28 : 19:08:19

The fields are within the SAME db. Is Update or Insert appropriate?

Or is it best to make a copy of the db and use Insert?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 19:33:41

depnds on what yo want to do.
If the rows are already there then updates - if not then inserts

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aniko
Starting Member

25 Posts

Posted - 2010-12-28 : 19:36:42

To elaborate (and make it simple) consider that I have one db with 3 ~~fields~~ columns (surname, address, and flag) with many records.

Some some address ~~fields~~ columns are empty, and some contain data. What I'd like to do is say... Update Address ~~field~~ column (b) with address ~~field~~ column (a) where surname (b) and surname (a) match. The flag ~~field~~ column identifies whether the address ~~field~~ column is NULL or not.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 19:42:38

Think you've answered your question - it will be an update.

You have columns in a table in a database not fields in a database.
Think that might be why you are struggling to understand what to do and formulate a question.
From what you say you have duplicate surnames so will have to decide what to do if they have multip[le ddresses - which address do you take to update those that are missing.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aniko
Starting Member

25 Posts

Posted - 2010-12-28 : 19:50:32

This is a unique case where I know that I do not have duplicate surnames with different addresses. I only have unique surnames and addresses. In fact, I will only have ONE instance. Problem is, some surnames simply contain NO addresses at all. I actually have identified which surnames are "the same" and in each case only one from the set contains an address.

UPDATE table
SET address = ???
WHERE surname = ???

I can't see how I can achieve this within the same table. If I had two identical tables I'm thinking...

INSERT INTO [table1] (address)
SELECT address from [Table2]
WHERE table1.surname = table2.surname

But I get The multi-part identifier "..." could not be bound error, which makes sense. But not sure how to work around this.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 20:15:35

You're still not defining the problem but this might help.

UPDATE table
SET address = (select address from table where surname = 'surnamewithaddress')
WHERE surname = 'surnamewithoutaddress'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aniko
Starting Member

25 Posts

Posted - 2010-12-28 : 22:11:00

Apologies if my explanation is not clear. Allow me to try again. I'll use a visual aid this time.

As you can see I've shown you 3 example records. The Match column identifies if they're "the same". What I'd like to do is where each record is the same (i.e. the Match column contents is identical) I want to update the empty address field with the address contents of the Master record, identified by the Master column YES.

There will only ever be one master of any records that are linked as a "match". And a Master is the only record that will contain an address.

This is what I'm struggling with.

aniko
Starting Member

25 Posts

Posted - 2010-12-28 : 23:13:04

No suggestions?

aniko
Starting Member

25 Posts

Posted - 2010-12-28 : 23:26:48

Also, I'm happy if the "matching" address is put into a new column. For example, Address2 - if this makes life easier.

aniko
Starting Member

25 Posts

Posted - 2010-12-29 : 00:56:30

Okay, I may have achieved what I wanted by making an exact copy of the table and using an INNER JOIN.

Select t1.surname, t2.address, t1.unique, t1.match, t1.master
From t1
INNER JOIN t2
ON t1.match = t2.match

Appears to have worked. Does anyone see any problems with such an approach?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 01:36:26

close

update t1
set address = t2.address
From tbl t1
INNER JOIN tbl t2
ON t1.match = t2.match
and t2.master = 'yes'
where t1.address is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aniko
Starting Member

25 Posts

Posted - 2010-12-29 : 04:04:55

Thanks. I'll give this a try tomorrow when I'm back at the machine.

aniko
Starting Member

25 Posts

Posted - 2010-12-29 : 16:17:23

Brilliant! I believe this has worked. :)

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources