#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    42
    Rep Power
    2

    Selecting and replacing query


    Hi!
    I work at a bank and currently we are having some trouble with a few values.. So what I need to do is replace this with some other values from the same table.

    Here goes:

    I have 3 colums. ex:

    Maturity------Moneyness---------Value
    1d...................1.0.......................2
    2d...................1.0.......................2.1
    1d....................2.0......................14
    2d....................2.0.......................16

    What I want to do is make the query replace the values of moneyness 2.0 to the same values as the moneyness 1.0 and selecting them while still keeping rest of the values..

    so I want it to look like this:

    Maturity------Moneyness---------Value
    1d...................1.0.......................2
    2d...................1.0.......................2.1
    1d....................2.0......................2 (used to be 14)
    2d....................2.0.......................2.1 (used to be 16)

    Right now it looks like this

    Code:
     sql_q = "SELECT maturity,moneyness,value "
        sql_q+= "FROM swapOptionVolatilities "
        sql_q+= "WHERE date = '%s' " % uploadDate.string('%Y-%m-%d')
        sql_q+= " AND Currency = '%s' " % Currency
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    42
    Rep Power
    2
    Think any of this might work?

    Code:
    --- update q1
    -- set value = q2.value
    select q1.*, q2.moneyness, q2.value, q2.maturity, q2.expiry
    	from dbo.swapOptionVolatilities q1, dbo.swapOptionVolatilities q2
    		where q1.date = q2.date
    			and q1.expiry = q2.expiry
    			and q1.maturity = q2.maturity
    			and q1.moneyness = 1.0
    			and q2.moneyness = 2.0
    			and q1.Currency = q2.Currency
    			and q1.[date] = '2013-09-12'
    			order by q1.maturity desc
    Code:
    select x1.*
    from dbo.swapOptionVolatilities as x1
    inner 
    	join dbo.swapOptionVolatilities as x2
    		on x2.maturity = x1.maturity
    		and x2.moneyness = '2.0'
    		and x1.moneyness = '1.0'
    		where x2.[date] = '2013-09-12'
    		order by x1.maturity
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by ChristopherL
    Think any of this might work?
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    42
    Rep Power
    2
    Originally Posted by r937
    what happened when you tested it?
    Solved it like this, since I am a rookie this solution took me awhile

    Code:
    -- saves values before updating into this dbo
    
    SELECT * INTO dbo.christophers_test FROM dbo.swapOptionVolatilities
    
    --- updates values in q1 to q2 values where q1 = -2.0 and q2 = -1.0, only for SEK.
    update q1
    set value = q2.value
    	from dbo.swapOptionVolatilities q1, dbo.swapOptionVolatilities q2
    		where q1.[date] = q2.[date]
    			and q1.expiry = q2.expiry
    			and q1.maturity = q2.maturity
    			and q1.moneyness = -2.0
    			and q2.moneyness = -1.0
    			and q1.Currency = 'SEK'
    			and q2.Currency = 'SEK'

IMN logo majestic logo threadwatch logo seochat tools logo