copy data from one column to another within the same table.
-
i've got a table [MyTable] with 3 columns.
account, column2, column3 (column3 is empty)
i basically have different accounts in the account column,
and need to copy some data from column2, to column3 based on
a certain account.
i've got hundreds of repeating records...
account: column2: column3:
101 recordx
102 recordx
103 recordx
104 recordx
105 recordx
what i'm really trying to do is move data from column2 to column3
and under a different account.
i'm thinking something like this:
UPDATE [MyTable]
SET column3 = column2 where account = '105' --trying to move records to account 105
FROM (SELECT column2 FROM [MyTable] WHERE account = '101')
apparently i'm doing something wrong. it's not working. what am i missing?
thanks in advance:)
_________________________
-
This is enough:
UPDATE [MyTable]
SET column3 = column2 where account = '105' --trying to move records to account 105
Remove:
FROM (SELECT column2 FROM [MyTable] WHERE account = '101')
-
many thanks...
_________________________
-
You are welcome.
-
one more question.
what if i wanted to select out certain data from column2 that i need to move
into column3?
maybe this?
UPDATE [MyTable]
SET column3 = column2 (SELECT column2 from [MyTable] WHERE column2 LIKE '%thisvalue%') where account = '105'
i think i'm starting to understand this stuff.
_________________________
-
UPDATE [MyTable]
SET column3 = column2
WHERE column2 LIKE '%thisvalue%'
AND account = '105'
-
thanks again !
_________________________
-
unfortunately; it's not working for me.
i've been reading about the differences between update & insert.
you UPDATE a column, but when breaking it down to certain rows
then it would be an INSERT because now i'm getting down to row
differences such as:
...WHERE column2 LIKE '%thisvalue%' AND account = '105'
in my case i'm looking for certain values in [column2], and want to insert them on
certain rows where [account] = 105 under [column3]
hate to drag this out, but just trying to get a grip on this process.
really greatful for your help thus far.
_________________________
-
If I'm understanding you correctly, I think your looking for a case statement - i.e.
update myTable
set column3 = case
when column2 like '%thisvalue1%' and account = '105' then 'somevalue1'
when column2 like '%thisvalue2%' and account = '105' then 'somevalue2'
else 'someothervalue'
end
from myTable
Tommy
Follow @sqlscribe -
Try this:
-- Create temporary tabledeclare @test table (acct int null, col1 int null, col2 int null)-- Insert test datainsert @test (acct, col1)select 101,1union select 102,2union select 103,3union select 104,4union select 105,5-- Display initial result setselect * from @test-- 'Insert' (Update) col1 for acct 101 into col2 for acct 105update @testset col2 = (select col1 from @test where acct = 101)where acct = 105--Display final resultselect * from @test
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply