Re: [sqlite] UPDATE Help
Thank you Simon. Will do. Shantanu From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Tuesday, October 14, 2014 11:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] UPDATE Help On 15 Oct 2014, at 1:10am, Shantanu Namjoshi wrote: > ALTER TABLE dailydelete ADD COLUMN SMB varchar(11); If you find yourself doing things like this your schema is messed up. SMB is obviously data, not a column. Redesign your table so that HML, SMB, UMD and Rf are values in a column, not the names of columns. By the way, SQLite doesn't have a varchar type. Your data will be interpreted as TEXT, and will not be truncated to 11 characters. It would be better just to declare the column as TEXT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://cp.mcafee.com/d/5fHCNEedEI8IIIcK6zCVKVJAsCVssqekjr8VdOXP3NJAsCVssqemjoUsej79II6WgRkhHgD8546WixZasTqI8tW3pIwTikfFjCXlx3LgrdzH6zDQ6m7-LO8VxMsDvHTbFIL6zDCkjhOy-UVORQr8FGTKVOEuvkzaT0QSyrhdTWVEVs73C4jhOO-rKr01Q5zVj_w0ajRTHU0xsxa62qKMM-l9OwXna2NYF-nrFYq6W2NYF-nrFYq5O5mUm-waEb7OD_00jrbBNNI5-Aq83iTgmfBfPiWq80C5wUg18Qq80LVWNJdwTvf_-a2 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE Help
Thank you sir. Sometimes my brain freezes are unbelievable! Shantanu From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Tuesday, October 14, 2014 11:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] UPDATE Help On Tue, Oct 14, 2014 at 8:10 PM, Shantanu Namjoshi < shantanu.namjo...@business.uconn.edu> wrote: > > UPDATE dailydelete > SET SMB = (SELECT dailyfactors.SMB FROM dailyfactors WHERE > dailydelete.data = dailyfactors.Date), > SET Rf = (SELECT dailyfactors.Rf FROM dailyfactors WHERE dailydelete.data > = dailyfactors.Date) > > WHERE EXISTS (select * from dailyfactors WHERE dailydelete.data = > dailyfactors.Date); > > > I get the following error: > Error: near "SET": syntax error > On the syntax diagram (http://cp.mcafee.com/d/2DRPoQ76Qm4mmm6hO8UTsSOejsKed7a9JAsCVtVxUSOejsKed7b9Ise79zASm3t8qG8REjA2y3t9g-BerJm4eZ1ISgrFa7QFPtGMxTEdCT_d7fEcIfZvAQTS4kuLsKCO-DP2bPXPDbnjIyyHtNfBgY-F6lK1FJ4SCrLRPhOUe7c8CzBBYTsS02Eb7OD_00sfS14lrmgSvY3zt1o-k_bJQ-d2V2Hsbvg5k5zVj_w09JAQsTjdwLQzh0qmW2NYF-qnjh04MI72096zh05_fmdFI6OETA) the SET keyword only occurs once. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://cp.mcafee.com/d/5fHCNEgdEI8IIIczAhNKVJAsCVssqekjr8VdOXP3NJAsCVssqemjoUsej79II6WgRkhHgD8546WixZasTqI8tW3pIwTikfFjCXlx3LgrdL-qevgpovW_9FLI8EZuVtdBZfC4nDTDemKDp55mXyvaxVZicHs3jqpJcTvHCzBMseohd7bbVKVI07gmfBf-00FfnuLw25O4Eo9GX33VkDa3JsEb7ODVtKDNErEb7ODVtKDNEn8lrxrW0GwIvavY01dICzCWpI5-Aq83iTgmfBfPiWq80C5wUg18Qq80LVWNJdwSbE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE Help
On Tue, Oct 14, 2014 at 8:10 PM, Shantanu Namjoshi < shantanu.namjo...@business.uconn.edu> wrote: > > UPDATE dailydelete > SET SMB = (SELECT dailyfactors.SMB FROM dailyfactors WHERE > dailydelete.data = dailyfactors.Date), > SET Rf = (SELECT dailyfactors.Rf FROM dailyfactors WHERE dailydelete.data > = dailyfactors.Date) > > WHERE EXISTS (select * from dailyfactors WHERE dailydelete.data = > dailyfactors.Date); > > > I get the following error: > Error: near "SET": syntax error > On the syntax diagram (http://www.sqlite.org/lang_update.html) the SET keyword only occurs once. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE Help
On 15 Oct 2014, at 1:10am, Shantanu Namjoshi wrote: > ALTER TABLE dailydelete ADD COLUMN SMB varchar(11); If you find yourself doing things like this your schema is messed up. SMB is obviously data, not a column. Redesign your table so that HML, SMB, UMD and Rf are values in a column, not the names of columns. By the way, SQLite doesn't have a varchar type. Your data will be interpreted as TEXT, and will not be truncated to 11 characters. It would be better just to declare the column as TEXT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE Help
Hello, I was writing for some help with a problem that has me stumped. I have two tables in a DB. Table1 = dailydelete Table2 = dailyfactors. dailyfactors is a list of dates and some corresponding information HML, SMB, UMD and Rf for each of these dates dates are NOT repeated in this table dailydelete is data for say 100 stocks for each of the dates in dailyfactors including price, return, etc. dates ARE repeated in this table, for each stock the table dailydelete has many more rows than the table dailyfactors. I want to add columns to dailydelete. I want to pull information from table dailyfactors corresponding to the date in each row in table dailydelete (I use varchar as some data may be missing or input as a character) The following is the code I use: ALTER TABLE dailydelete ADD COLUMN SMB varchar(11); ALTER TABLE dailydelete ADD COLUMN HML varchar(11); ALTER TABLE dailydelete ADD COLUMN UMD varchar(11); ALTER TABLE dailydelete ADD COLUMN Rf varchar(11); UPDATE dailydelete SET SMB = (SELECT dailyfactors.SMB FROM dailyfactors WHERE dailydelete.data = dailyfactors.Date), SET HML = (SELECT dailyfactors.HML FROM dailyfactors WHERE dailydelete.data = dailyfactors.Date), SET UMD = (SELECT dailyfactors.UMD FROM dailyfactors WHERE dailydelete.data = dailyfactors.Date), SET Rf = (SELECT dailyfactors.Rf FROM dailyfactors WHERE dailydelete.data = dailyfactors.Date) WHERE EXISTS (select * from dailyfactors WHERE dailydelete.data = dailyfactors.Date); I get the following error: Error: near "SET": syntax error I have tried many different combinations of the above statement, but keep getting a syntax error somewhere or the other. Can someone please help and point out what I am doing wrong? Thank you for your help Shantanu Namjoshi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users