Re: [sqlite] UPDATE Help

2014-10-16 Thread Shantanu Namjoshi 
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 
<shantanu.namjo...@business.uconn.edu> 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

2014-10-16 Thread Shantanu Namjoshi 
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

2014-10-14 Thread Richard Hipp 
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

2014-10-14 Thread Simon Slavin 

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

2014-10-14 Thread Shantanu Namjoshi 
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