Databases
StackOverflow2013
Postsdbo
POConvert mysql SELECT using OUTER JOIN to an UPDATE
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POConvert mysql SELECT using OUTER JOIN to an UPDATE
    primarykey
    data
    text
    <p>I'm trying to update a table containing stock pricing information to include the returns of those stocks if held for different time periods. The return is inserted at the start of the time period. The following SELECT statement calculates the values I'm looking for, for 3 and 6 month hold periods. The table is <code>price_returns</code></p> <pre><code>SELECT curr.date, curr.company, curr.price, (mo3.price - curr.price)/curr.price AS 3MONTH_RETURN, (mo6.price - curr.price)/curr.price AS 6MONTH_RETURN FROM price_returns AS curr LEFT OUTER JOIN price_returns AS mo3 ON ((curr.date = LAST_DAY(mo3.date - INTERVAL 3 MONTH)) AND (curr.company = mo3.company)) LEFT OUTER JOIN price_returns AS mo6 ON ((curr.date = LAST_DAY(mo6.date - INTERVAL 6 MONTH)) AND (curr.company = mo6.company)) Order by curr.company, curr.date; </code></pre> <p>I would like convert this <code>SELECT</code> to an <code>UPDATE</code> into the table <code>price_returns</code> and insert <code>3MONTH_RETURN</code> and <code>6MONTH_RETURN</code> into columns <code>ret_3mth</code> and <code>ret_6mth</code>.</p> <p>This is what I have, but it's not executing the update correctly. The join appears to be working as it's matched the correct number of rows, but it doesn't change any rows.</p> <p><strong>* Update *</strong></p> <p>Not sure why, but this code below now appears to be working. Sorry for the confusion. I still haven't worked out what happened and why it wasn't but now is working. User error I'm sure.</p> <p><strong>* Update *</strong></p> <pre><code>UPDATE price_returns AS curr LEFT OUTER JOIN price_returns AS mo3 ON ((curr.date = LAST_DAY(mo3.date - INTERVAL 3 MONTH)) AND (curr.company = mo3.company)) LEFT OUTER JOIN price_returns AS mo6 ON ((curr.date = LAST_DAY(mo6.date - INTERVAL 6 MONTH)) AND (curr.company = mo6.company)) SET curr.ret_3mth = (mo3.price - curr.price)/curr.price, curr.ret_6mth = (mo6.price - curr.price)/curr.price; </code></pre> <p>Thanks for your help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USgetting-there
    1. USgetting-there
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POConvert mysql SELECT using OUTER JOIN to an UPDATE
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POConvert mysql SELECT using OUTER JOIN to an UPDATE
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload