UPDATE FROM syntax error

(1) By anonymous on 2022-01-29 16:45:20 [link] [source]

Hello, 

I'm pulling data from my energy provider as a rawTable and then would like to perform some calculations to place the data in an easier way to search as well as to do calculations. 

The data I receive from the energy supplier lands in this table: 

CREATE TABLE "rawData" (
	"ID"	INTEGER NOT NULL,
	"consumption"	REAL NOT NULL,
	"startTime"	TEXT NOT NULL,
	"endTime"	TEXT NOT NULL,
	PRIMARY KEY("ID" AUTOINCREMENT)
);

I then created this table to hold the data I want per day. 

CREATE TABLE "structuredData" (
	"rowID"	INTEGER NOT NULL,
	"Year"	INTEGER NOT NULL,
	"Month"	INTEGER NOT NULL,
	"Day"	INTEGER NOT NULL,
	"PeakConsumption"	REAL,
	"OffPeakConsumptioon"	REAL,
	PRIMARY KEY("rowID" AUTOINCREMENT)
);

To get the offpeak consumption from the rawData, I run the following query: 

INSERT INTO structuredData (Year, Month, Day, OffPeakConsumptioon)
SELECT strftime('%d', startTime) as valDay,
strftime('%m', startTime) as valMonth,
strftime('%Y', startTime) as valYear,
SUM(consumption) as valTotalDay
FROM rawData
WHERE
strftime('%Y', startTime)>='2021'
AND (strftime('%H:%M:%S',startTime) >= "00:30:00"
AND strftime('%H:%M:%S',startTime) < "04:30:00")
GROUP BY valYear, valMonth, valDay ;

Then I need to essentially do the same to the Peak Consumption and came up with this query: 

UPDATE structuredData 
SET PeakConsumption = daily.amt
FROM (SELECT SUM(consumption) as amt, strftime('%d', startTime) as valDay,	strftime('%m', startTime) as valMonth,	strftime('%Y', startTime) as valYear
				FROM rawData
				WHERE	strftime('%Y', startTime)>='2021'
				AND (strftime('%H:%M:%S',startTime) >= "00:00:00" AND strftime('%H:%M:%S',endTime) <= "00:30:00"
				OR strftime('%H:%M:%S',startTime) >= "04:30:00" AND strftime('%H:%M:%S',endTime) <="23:30:00")
				GROUP BY valYear, valMonth, valDay) AS daily
 WHERE structuredData.Day = daily.valDay
 and structuredData.Month = daily.valMonth
 and structuredData.Year = daily.valYear;

However, this tells me there's an error. 

Execution finished with errors.
Result: near "FROM": syntax error
At line 1:
UPDATE structuredData 
SET PeakConsumption = 1
FROM

Any ideas where I may be messing up??

(2) By Larry Brasfield (larrybr) on 2022-01-29 17:23:14 in reply to 1 [link] [source]

Because I saw nothing wrong with your DML, I began a piecemeal substitution process which was equally puzzling until I did some reformatting. After getting it into more readable form, I had this:UPDATE structuredData SET PeakConsumption = daily.amt FROM (SELECT SUM(consumption) as amt, strftime('%d', startTime) as valDay, strftime('%m', startTime) as valMonth, strftime('%Y', startTime) as valYear FROM rawData WHERE strftime('%Y', startTime)>='2021' AND (strftime('%H:%M:%S',startTime) >= "00:00:00" AND strftime('%H:%M:%S',endTime) <= "00:30:00" OR strftime('%H:%M:%S',startTime) >= "04:30:00" AND strftime('%H:%M:%S',endTime) <="23:30:00") GROUP BY valYear, valMonth, valDay) AS daily WHERE structuredData.Day = daily.valDay and structuredData.Month = daily.valMonth and structuredData.Year = daily.valYear;

To my eye, this was "the same" except for whitespace, which was acting kind of strangely as I replaced it with spaces. Strangely, after reformatting, your DML runs without any syntax error complaints.

I leave it to you to hunt down the strangeness in your "whitespace".

P.S. I do not claim this is good formatting. I was in a hurry.

(3) By anonymous on 2022-01-29 17:40:40 in reply to 2 [source]

Hello,

I managed to understand where the error was and it turned out to be the version of DB Browser for SQLite that didn't like the FROM. After upgrading it, the query runs, but doesn't affect any data.

(4) By Ryan Smith (cuz) on 2022-01-29 18:11:50 in reply to 3 [link] [source]

Please post some example data - there are many possible reasons why the queries won't work, such as time-spans that are too wide in the input data, but without some input data, we will just be guessing.

Also, your table structures are not optimal. I'm going to imagine the original input table "rawData" to already exist or be provided, so will not critique that, but the new derivative table "structuredData" can be improved.

I'm avoiding fields named like SQLite internals, such as rowid, and assuming you meant for the rowid field to imply the real rowid, so fixing that too, the table schema looks like this:

CREATE TABLE "structuredData" (
  ID        INTEGER PRIMARY KEY,
  LogYear   INTEGER NOT NULL,
  LogMonth  INTEGER NOT NULL,
  LogDay    INTEGER NOT NULL,
  PeakConsumption     REAL,
  OffPeakConsumptioon REAL
);

Not sure if that is fully optimal either, but will need to see some data, let's say 2-days worth, to make any useful guesses.

(5) By Keith Medcalf (kmedcalf) on 2022-01-29 21:28:31 in reply to 1 [link] [source]

Very interesting, however, the sum function returns the sum, not the peak. max is the agregate that returns the peak. That is, sum returns the Area Under the Curve, and max returns the height of the curve at its tallest point (the peak).