SQLite SQL Help

[gjr80]

Really an SQL question but directly related to Weewx so I was hoping some SQL gurus can help, afraid my SQL knowledge is at best uneducated novice. To cut a long story short I have two SQLite databases, lets say weewx.sdb and dest.sdb, weewx.sdb is my standard weewx database containing my archive table and data from day 1, dest.sdb also contains a table named archive with the standard dateTime, usUnits and interval fields with a few other custom observational fields but only has a subset of the dateTimes that are in weewx.sdb. What I would like to achieve is to update the (let's say) outTemp field in the records in archive in dest.sdb with the same observation (outTemp) for the same dateTime in archive in weewx.sdb. So if the 1417214700 record in archive in my weewx.sdb has an outTemp value of 21.7 I want the 1417214700 record in archive in dest.sdb to have its outTemp value set to 21.7.

Like I said my SQL skils are rudimentary, basic queries I can handle but when you start adding JOINS or sub queries I start to get lost. I thought I had found an example I could plagiarise, I ended up doing the following

$ sqlite3 dest.sdb
> ATTACH '/home/gary/weewx.sdb' AS SOURCE;
> UPDATE archive SET outTempDay = (SELECT SOURCE.archive.outTemp FROM SOURCE.archive WHERE SOURCE.archive.dateTime = archive.dateTime) WHERE EXISTS (SELECT * FROM SOURCE.archive WHERE SOURCE.archive.dateTime = archive.dateTime);

but (of course) it does not work, it gives me 21.83333 in every outTemp field in archive in dest.sdb. 21.83333 happens to be the very first outTemp in my main weewx archive.

I have not been able to find any clues in the forums and my Googling is not yielding anything of much use (to me).  Any ideas?

Gary

[vds]

Gary - are you trying to seed the new v3 weewx-wd db or something like that ?

Looks like sqlite3 does not support dropping columns so the procedure seems a bit convoluted.  See the most upvoted reply to http://stackoverflow.com/questions/5938048/delete-column-from-sql-table and see if that helps any.

Untested but maybe try this ???

• create a temporary table in (a copy of) weewx.sdb with just the fields you want
• then dump that table to a .dump file
• then drop the temporary table
• and lastly restore the .dump file to seed the new weewx-wd db 
• (and I suppose you could do the same thing to remove the to-be-extraneous fields from the weewx.sdb too)

[vds]

ok - this is tested and seems to work ok so it might be in the ballpark....

# create a temporary table with the desired fields and records from the archive table

sqlite3 weewx.sdb

SQLite version 3.7.13 2012-06-11 02:05:22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> create table temptable(dateTime,usUnits,interval,extraTemp1);

sqlite> insert into temptable select dateTime,usUnits,interval,extraTemp1 from archive where extraTemp1 is not null;

sqlite> .quit

# dump the temporary table to a dump file, then nuke the temporary table to clean up your weewx.sdb database

echo ".dump weewx_wd" | sqlite3 weewx.sdb > weewx-wd.dump

echo "drop table weewx_wd;" | sqlite3 weewx.sdb

# restore the .dump file, changing the table name you'll create to be 'archive' for consistency  

cat weewx-wd.dump | sed -e s/temptable/archive/g | sqlite3 weewx-wd.sdb

You could use a similar procedure to nuke the extra columns from weewx.sdb probably.

I tried to do it with a true temporary table ala the link I posted earlier, but couldn't get the dump syntax to an external file quite right, so I punted and low-tech'd it.  Hope this helps some...

[Andrew Milner]

I think he is updating another unrelated database with outTemp data from the weewx database for corresponding times, and not dropping columns.  The simplest solution, since sqlite does not support update with join would be to migrate to MySQL where a join would achieve the desired result.

My SQL is not great, but this may work .....

UPDATE archive SET outTempDay = SOURCE.archive.outTemp WHERE dateTime IN (SELECT dateTime, outTemp FROM SOURCE.archive WHERE dateTime = SOURCE.archive.dateTime;);

or something along these lines anyway - using a subquery IS allowed in the UPDATE clause it would appear...

http://www.tutorialspoint.com/sqlite/sqlite_sub_queries.htm

[gjr80]

Thanks for the pointers. Finally got back to looking at this but was still having no luck. Knowing a sub-query was the solution some better directed Googling revealed numerous different posts on various sites each providing a solution very similar to the query I originally posted and there were followup posts saying the query worked so it made me wonder if using an attached database was the issue as that was the only real difference I could see. So I copied the source table into my database and voila my original query worked.
 
Just fo rthe record the following commands did the trick

sqlite3 weewx.sdb
.schema archive

Copy the resulting CREATE statement

.quit
sqlite3 dest.sdb

Paste in the previously copied CREATE statement changing the table name (I used warchive) if it already exists in dest.sdb and execute the CREATE statement

ATTACH 'weewx.sdb' as WW;
INSERT INTO warchive SELECT * FROM WW.archive;
UPDATE archive SET outTempDay = (SELECT warchive.outTemp FROM warchive WHERE archive.dateTime = warchive.dateTime) WHERE EXISTS (SELECT * FROM warchive WHERE archive.dateTime = warchive.dateTime);
DROP TABLE warchive;
.quit

dest.sdb will still be somewhat bloated despite dropping the table so do an sqlite VACUUM. From the bash prompt:

sqlite3 dest.sdb "VACUUM;"

Probably an unnecesarily long winded process but it works.

Gary








So I guess the using the attached datbase (well atleast with the syntax I was using) was the source of the problem.

[Andrew Milner]

Well am glad you got it working in the end - that's always the main thing one aims for!!!

Congrats on your success.

--
You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/oCF6kmcoEwc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[vds]

On Tuesday, December 2, 2014 6:49:57 AM UTC-8, gjr80 wrote:

ATTACH 'weewx.sdb' as WW;
INSERT INTO warchive SELECT * FROM WW.archive;
UPDATE archive SET outTempDay = (SELECT warchive.outTemp FROM warchive WHERE archive.dateTime = warchive.dateTime) WHERE EXISTS (SELECT * FROM warchive WHERE archive.dateTime = warchive.dateTime);
DROP TABLE warchive;
.quit

Wow, if that doesn't drive somebody to drink, nothing will :-)

 

dest.sdb will still be somewhat bloated despite dropping the table so do an sqlite VACUUM. From the bash prompt:

sqlite3 dest.sdb "VACUUM;"

Didn't know about that one.  Pretty slick.

Thanks! 

[Andrew Milner]

HIC

Slurp

HIC