October 30, 2007 at 5:35 pm
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:)
_________________________
October 31, 2007 at 12:05 am
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')
October 31, 2007 at 6:28 am
many thanks...
_________________________
October 31, 2007 at 6:32 am
You are welcome.
October 31, 2007 at 6:48 am
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.
_________________________
October 31, 2007 at 8:34 am
UPDATE [MyTable]
SET column3 = column2
WHERE column2 LIKE '%thisvalue%'
AND account = '105'
October 31, 2007 at 8:53 am
thanks again ! 🙂
_________________________
October 31, 2007 at 11:46 am
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.
_________________________
October 31, 2007 at 12:11 pm
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 @sqlscribeOctober 31, 2007 at 2:54 pm
Try this:
-- Create temporary table
declare @test-2 table (acct int null, col1 int null, col2 int null)
-- Insert test data
insert @test-2 (acct, col1)
select 101,1
union select 102,2
union select 103,3
union select 104,4
union select 105,5
-- Display initial result set
select * from @test-2
-- 'Insert' (Update) col1 for acct 101 into col2 for acct 105
update @test-2
set col2 = (select col1 from @test-2 where acct = 101)
where acct = 105
--Display final result
select * from @test-2
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply