Question 212697 in Mysql Question posted in
The official MySQL documentation can be found here.

Having error when trying to use 'From' clause in an 'Update' statement in MySQL Workbench

I’m pretty new to SQL so sorry if this is a dumb question.

I have a large table containing most of the counties from every state with a median home listing price. Each county/state has multiple observations because it has a new observation for every quarter for the past five years. I’m trying to create a new column that has the percentage change in median listing price from the previous quarter.
mySQL says that I have a syntax error, but I can’t for the life of me figure it out. Any help is appreciated.

Here is my code:

UPDATE housing AS current
    SET price_change =((current.medListingPrice - previous.prevMLP)/ previous.prevMLP)*100
FROM
    (SELECT
        county,
        state,
        year,
        quarter,
        medListingPrice,
        LAG(medListingPrice) OVER (PARTITION BY county, state ORDER BY year, quarter) AS prevMLP
    FROM housing) AS previous
    WHERE current.county = previous.county
        AND current.state = previous.state
        AND current.quarter = previous.quarter
        AND current.year = previous.year;
Tags:

2

Answers


  1. 0

    Your SQL query looks mostly correct, but there’s a small mistake in the UPDATE statement’s syntax. In MySQL, the UPDATE statement doesn’t use a FROM clause like some other SQL databases do. Instead, you can achieve what you want using a subquery in the SET clause directly. Here’s the modified query:

    UPDATE housing AS current
SET current.price_change = ((current.medListingPrice - previous.prevMLP) / previous.prevMLP) * 100
WHERE (current.year, current.quarter) = (previous.year, previous.quarter)
    AND current.county = previous.county
    AND current.state = previous.state;

    In this modified version, we directly use a subquery in the SET clause to calculate the price_change. The subquery you had previously used in the FROM clause is not necessary.

    Also, note that I replaced the condition current.year = previous.year with (current.year, current.quarter) = (previous.year, previous.quarter). This is because you are looking to calculate the percentage change based on the previous quarter’s median listing price, and for that, you need to match both the year and quarter to get the correct previous value.

    With this modification, your query should work as expected in MySQL.

    Login or Signup to reply.
  2. 0 
    UPDATE housing AS current
JOIN (
    SELECT
        id,
        county,
        state,
        year,
        quarter,
        medListingPrice,
        LAG(medListingPrice) OVER (PARTITION BY county, state ORDER BY year, quarter) AS prevMLP
    FROM housing
) AS previous ON current.id = previous.id  -- Replace 'id' with the unique identifier column
SET current.price_change = ((current.medListingPrice - previous.prevMLP) / previous.prevMLP) * 100;

    UPDATE statement in MySQL doesn’t support the FROM clause directly
    so I use a subquery with the useful data

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search