MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

copying a column within the same table

Discuss copying a column within the same table in the MS SQL Development forum on Dev Shed.
copying a column within the same table MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old May 27th, 2004, 09:28 AM
yman yman is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 1 yman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
copying a column within the same table
I need to copy a column which contains a list of integers to a new column. Can someone please tell me how I can do this using a query statement? Thanks in advance.
Reply With Quote
  #2  
Old May 28th, 2004, 02:42 AM
brightlight brightlight is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2004
Posts: 371 brightlight User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 h 32 m 27 sec
Reputation Power: 5
Hi,

Assume tbl is a table and Old is the old column and New is the new column. If u want to copy the values from Old to New, then run the following MS SQL stmt.
Quote:
UPDATE tbl SET New = Old
__________________
Regards,
Brightlight
Reply With Quote
  #3  
Old July 10th, 2004, 05:29 PM
PHP NewBee PHP NewBee is offline
Permanently Banned
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Switzerland
Posts: 220 PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 21 m 42 sec
Warnings Level: 10
Number of bans: 1
Reputation Power: 0
Quote:
Originally Posted by brightlight
Hi,

Assume tbl is a table and Old is the old column and New is the new column. If u want to copy the values from Old to New, then run the following MS SQL stmt.

OK, what if I wish to copy a column from table1 and insert it to table2 ? how can you do this in mysql or SQL command please???
Reply With Quote
  #4  
Old July 10th, 2004, 06:58 PM
PHP NewBee PHP NewBee is offline
Permanently Banned
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Switzerland
Posts: 220 PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 21 m 42 sec
Warnings Level: 10
Number of bans: 1
Reputation Power: 0
brightlight

I can already say thank you, as your post did help me big time.


still please, if you can answer my previous question in the previous post..... please do.
Reply With Quote
  #5  
Old July 11th, 2004, 12:50 AM
Username=NULL Username=NULL is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2004
Location: TX
Posts: 249 Username=NULL User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5
Send a message via Yahoo to Username=NULL
If you just want to copy the column's data then just Select Into another table to hold it till you want to do whatever it is you want to do w/it...
Code:
select	[origField]
into	newTable
from	origTable --origTable being the table that holds the orig column

Else, if you want to do as BrightLight was getting @...Give this a shot...
Code:
--table2 is the target table, table1 has the orig data to be copied
update	table2
inner	join table1 on table2.[relatedField] = table1.[relatedField]
set	table2.[targetField] = table1.[origField]
Reply With Quote
  #6  
Old July 11th, 2004, 08:09 PM
PHP NewBee PHP NewBee is offline
Permanently Banned
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Switzerland
Posts: 220 PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 21 m 42 sec
Warnings Level: 10
Number of bans: 1
Reputation Power: 0
i feel kind of ashamed to say i tried the script you propose.... and :$ it did not work .. maybe I did something wrong.


but are you sure ?
Reply With Quote
  #7  
Old July 12th, 2004, 12:06 AM
Username=NULL Username=NULL is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2004
Location: TX
Posts: 249 Username=NULL User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5
Send a message via Yahoo to Username=NULL
maybe, then again, it may be my code Post it and lets see if we can't get you rolling.
Reply With Quote
  #8  
Old July 13th, 2004, 08:54 PM
PHP NewBee PHP NewBee is offline
Permanently Banned
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Switzerland
Posts: 220 PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 21 m 42 sec
Warnings Level: 10
Number of bans: 1
Reputation Power: 0
Quote:
Originally Posted by Username=NULL
maybe, then again, it may be my code Post it and lets see if we can't get you rolling.
ok here you go



Let me tell you first what I did.



I phpmyadmin exported table1, that is evo_blogs, changed its name in the dump to test_copy_column, removed the "blog_shortname" content, and pasted the modified dump into the SQL table.



Thus test_copy_column is EXACTLY a copy of evo_blogs, except for the missing "blog_shortname" column content.



Let us try to copy this from evo_blogs, table 1, to test_copy_column, table 2 (both tables within the same DB)



table 1 `evo_blogs`

table 2 `test_copy_column`

column to be copied `blog_shortname`

your code:

PHP Code:
 update table2

inner join table1 on table2.[relatedField] = table1.[relatedField]

set table2.[targetField] = table1.[origField] 



thus

PHP Code:
 update `test_copy_column`

inner join `evo_blogs` on `test_copy_column`.[`blog_shortname`] = `evo_blogs`.[`blog_shortname`]

set `test_copy_column`.[`blog_shortname`] = `evo_blogs`.[`blog_shortname`] 




========================================

result:

PHP Code:
 Database tastersh_bvlt1 - Table test_copy_column running on localhost 

Error

SQL-query : 

UPDATE `test_copy_column` INNER JOIN `evo_blogs` ON `test_copy_column`.[ `blog_shortname` ] = `evo_blogs`.[ `blog_shortname` ] SET `test_copy_column`.[ `blog_shortname` ] = `evo_blogs`.[ `blog_shortname` ] 

MySQL said: 

 

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '[ `blog_shortname` ] = `evo_blogs`.[ `blog_shortname` ] SET ` 



================================================================================

is it the apostrophe like things? , let us remove those and try:

PHP Code:
 update test_copy_column

inner join evo_blogs on test_copy_column.[blog_shortname] = evo_blogs.[blog_shortname]

set test_copy_column.[blog_shortname] = evo_blogs.[blog_shortname] 



=================================================================================

result:

PHP Code:
 Database tastersh_bvlt1 - Table test_copy_column running on localhost 

Error

SQL-query : 

UPDATE test_copy_column INNER JOIN evo_blogs ON test_copy_column.[blog_shortname] = evo_blogs.[blog_shortname] SET test_copy_column.[blog_shortname] = evo_blogs.[blog_shortname] 

MySQL said: 

 

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '[blog_shortname] = evo_blogs.[blog_shortname] SET test_copy_col 
Reply With Quote
  #9  
Old July 14th, 2004, 12:54 AM
brightlight brightlight is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2004
Posts: 371 brightlight User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 h 32 m 27 sec
Reputation Power: 5
I'm not familiar with PHP code but for the SQL query syntax its not necessary for the quotes and square brackets []. Check without the brackets and see what u get.

Quote:
update test_copy_column

inner join evo_blogs on test_copy_column.blog_shortname= evo_blogs.blog_shortname

set test_copy_column.blog_shortname = evo_blogs.blog_shortname
Reply With Quote
  #10  
Old July 14th, 2004, 01:55 AM
PHP NewBee PHP NewBee is offline
Permanently Banned
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Switzerland
Posts: 220 PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 21 m 42 sec
Warnings Level: 10
Number of bans: 1
Reputation Power: 0
Quote:
Originally Posted by brightlight
I'm not familiar with PHP code but for the SQL query syntax its not necessary for the quotes and square brackets []. Check without the brackets and see what u get.


true .....


and no error .... but the column was not copied anyway...

Affected rows: 0 (Query took 0.0004 sec)

SQL-query : [Edit] [Create PHP Code]
UPDATE test_copy_column INNER JOIN evo_blogs ON test_copy_column.blog_shortname = evo_blogs.blog_shortname SET test_copy_column.blog_shortname = evo_blogs.blog_shortname
Reply With Quote
  #11  
Old July 14th, 2004, 02:25 AM
brightlight brightlight is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2004
Posts: 371 brightlight User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 h 32 m 27 sec
Reputation Power: 5
Yes, definitely the columns will not be copied because from the syntax suggested by username=null the related field and the target field both should be different. In ur sql the related and the target field are the same.

when ur test_copy_column.blog_shortname is null then it is never going to satisfy the following condition

Quote:
test_copy_column.blog_shortname = evo_blogs.blog_shortname


Use a common field such as id or something to satisfy the above condition then the columns will be copied.
I'll give u a sample
Quote:
test_copy_column Table

id shortname
1 <null>
2 <null>
3 <null>

evo_blogs table

id shortname
1 abc
2 def
3 gfh
Reply With Quote
  #12  
Old July 14th, 2004, 07:37 PM
PHP NewBee PHP NewBee is offline
Permanently Banned
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Switzerland
Posts: 220 PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level)PHP NewBee User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 21 m 42 sec
Warnings Level: 10
Number of bans: 1
Reputation Power: 0
Hi

I filled all the rows of the targeted field with data, and the script still did not work

I tried editing the type of column, and made test_copy_column.blog_shortname as Not Null, and TEXT....... still did not work.
Reply With Quote
  #13  
Old July 14th, 2004, 08:25 PM
brightlight brightlight is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2004
Posts: 371 brightlight User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 h 32 m 27 sec
Reputation Power: 5
can u give me the table structure of ur two tables and let me see what can be done.
Reply With Quote
  #14  
Old July 14th, 2004, 10:08 PM
Username=NULL Username=NULL is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2004
Location: TX
Posts: 249 Username=NULL User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5
Send a message via Yahoo to Username=NULL
BrightLight is on top of it...to nail your solution you really have to have a fundemental understanding of how a inner join works. Go back to my first reply for a sec...well, here, just refer to this...
Code:
update	table2
inner	join table1 on table2.[relatedField] = table1.[relatedField]
set	table2.[targetField] = table1.[origField]

In order to do a join on the 2 tables, you need to define the relation to join on. I don't know your exact structure so my "code" is just skeleton code for you to follow. Refer to this visual, it may help...
Code:
Table1		Table2
------		------
relatedField--> relatedField
origField	targetField

...you see there are 2 columns in each table. The 2 tables are joined w/the related fields, once that is established, the origField from Table1 can then be copied (really doing an Update) to Table2. You're trying to join AND do the update all w/the same columns, possible, but w/your example it's not gonna work.

Oh ya, fyi...if you're using MySQL there is a seperate forum for MySQL, they may be able to find syntax errors better.

One more thing...answer me this if you don't mind, are you just copying that 1 column from the table into another table?...is that you're objective? If so, my first snip of code (Select INTO) will work as well...maybe give it a shot?
Reply With Quote
Reply