Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Unanswered: Update table in Sql

    I have a table called Table_A and it looks like this
    Acccde IndexName Quarter SixMonths
    101 Portfolio -2.3100 10.5
    102 Portfolio -4.1700 12.6
    103 Portfolio -1.5000 15.4
    104 Portfolio 1.8900 9.5

    I want to update Table_B column 6Months with values from Table_A.SixMonths
    Acccde SixMonths
    101
    102
    103
    104


    At the end Table_B should lok like
    Acccde SixMonths
    101 10.5
    102 12.6
    103 15.4
    104 9.5
    Last edited by msenoelo; 01-13-04 at 03:34.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    update 
    	b
    set 
    	b.SixMonths = a.SixMonths
    from 
    	Table_A a,
    	Table_B b
    where
    	a.acccde = b.acccde
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    Let me re-phrase my question
    I have a table called Table_A and it looks like this
    Acccde IndexName Quarter SixMonths
    101 Portfolio -2.3100 10.5
    102 Portfolio -4.1700 12.6
    103 Portfolio -1.5000 15.4
    104 Portfolio 1.8900 9.5
    let me re-phrase my
    I want to update Table_B column 6Months with values from Table_A.SixMonths
    Acccde 3months 3months Six_Months
    101 10 -2.3100 Null
    102 20 -4.1700 Null
    103 30 -1.5000 Null
    104 40 1.8900 Null

    At the end Table_B should lok like
    Acccde 3months 3months Six_Months
    101 10 -2.3100 10.5
    102 20 -4.1700 12.6
    103 30 -1.5000 15.4
    104 40 1.8900 9.5


    Last edited by msenoelo

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    update 
    	b
    set 
    	b.Six_Months = a.SixMonths
    from 
    	Table_A a,
    	Table_B b
    where
    	a.acccde = b.acccde
    Either the above code will work or i am not getting what you want to say ...

    Do you have the table_b created like this


    Acccde 3months 3months Six_Months
    101 10 -2.3100 Null
    102 20 -4.1700 Null
    103 30 -1.5000 Null
    104 40 1.8900 Null


    or do you want to do that too. ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    Sorry this is my mistake
    QuarterTo and Six_Months_to from table_A i calculated them with the following statement
    select Periodic from ISLPPtbl_Irr_Info where DATEDIFF ( mm , periodstart , periodend ) = 3, then I put the values in Table_A in Quarter_to, now I want to update Table_B column 6months with the same select statement = 6 not 3 that is (select Periodic from ISLPPtbl_Irr_Info where DATEDIFF ( mm , periodstart , periodend ) = 6) when i do that i get this error (Subquery returned more than 1 value. This is not permitted when the ****subquery follows =, !=, <, <= , >, >= or when the subquery is used as an ****expression. ****The statement has been terminated.) it is becaust that select returns
    multiple rows

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Man .. I Hate to say this but

    The problem you are facing is not getting across to me ....
    If you can post some ddl and the thing you are trying to achive , we may be able to help you.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    Ok let me start from scratch maybe and make it simple
    I have Table_O
    acccde periodstart periodend periodic
    6513733 2002-09-30 2002-12-31 -3.18
    6513733 2002-06-28 2002-12-31 -11.30
    6513766 2002-09-30 2002-12-31 -0.29
    6513766 2002-06-28 2002-12-31 -5.28
    6513774 2002-09-30 2002-12-31 -1.52
    6513774 2002-06-28 2002-12-31 -7.04

    Then I want to take insert into a new Table_A
    If the difference between periodstart and periodend is 3 update Table_A Quarter Column with the periodic and if difference between periodstart and periodend is 6 them insert into Table_A SixMonths columns with periodic. This means Table_A must have the following results
    Acccde Quarter SixMonths
    6513733 -3.18 11.30
    6513766 -0.29 -5.28
    6513774 -1.59 -7.04

    So basically i need one acccde with the right quater and sixmonthsto

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by msenoelo
    Ok let me start from scratch maybe and make it simple
    I have Table_O
    acccde periodstart periodend periodic
    6513733 2002-09-30 2002-12-31 -3.18
    6513733 2002-06-28 2002-12-31 -11.30
    6513766 2002-09-30 2002-12-31 -0.29
    6513766 2002-06-28 2002-12-31 -5.28
    6513774 2002-09-30 2002-12-31 -1.52
    6513774 2002-06-28 2002-12-31 -7.04

    Then I want to take insert into a new Table_A
    If the difference between periodstart and periodend is 3 update Table_A Quarter Column with the periodic and if difference between periodstart and periodend is 6 them insert into Table_A SixMonths columns with periodic.

    This means Table_A must have the following results
    Acccde Quarter SixMonths
    6513733 -3.18 11.30
    6513766 -0.29 -5.28
    6513774 -1.59 -7.04

    So basically i need one acccde with the right quater and sixmonthsto
    Code:
    select 
    	acccde, 
    	sum (Quarter) Quarterly, 
    	sum (SixMOnths) SixMonths 
    from 
    	(
    		select 
    			acccde ,
    			isnull((select periodic where datediff(mm,periodstart,periodend) = 3),0) "Quarter" ,
    			isnull((select periodic where datediff(mm,periodstart,periodend) = 6),0) "SixMonths" 
    		from 
    			table_O 
    	) a 
    group by 
    	acccde
    I think ... this will do it
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Thumbs up

    Thanks it worked perfectly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •