Re: [sqlite] Adding a record to a table with one value change
Keith Medcalf, on Friday, November 15, 2019 03:50 PM, wrote...
>
>
> How you would use bound parameters depends on what you are using to interface
> with the sqlite3 database.
>
> https://www.sqlite.org/c3ref/bind_blob.html for the C interfaces.
>
> In something like python you would pass the bindings as a tuple to the
> execute method of the cursor:
>
> cr.execute(sql, ('p0001', 5, '2014-02-23'))
>
> Basically it allows you to substitute values provided by your program into
> the sql statement rather than
> compose the sql statement dynamically possibly leading to injection problems.
Thanks. I am going to read about. The D libraries that I am using has it. I
just never knew about it. Thanks.
> https://xkcd.com/327/
Pretty funny stuff... thanks.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
How you would use bound parameters depends on what you are using to interface
with the sqlite3 database.
https://www.sqlite.org/c3ref/bind_blob.html for the C interfaces.
In something like python you would pass the bindings as a tuple to the execute
method of the cursor:
cr.execute(sql, ('p0001', 5, '2014-02-23'))
Basically it allows you to substitute values provided by your program into the
sql statement rather than compose the sql statement dynamically possibly
leading to injection problems.
https://xkcd.com/327/
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Jose Isaias Cabrera
>Sent: Friday, 15 November, 2019 06:20
>To: SQLite mailing list
>Subject: Re: [sqlite] Adding a record to a table with one value change
>
>
>Keith Medcalf, on Thursday, November 14, 2019 06:44 PM, wrote...
>>
>>
>> On Thursday, 14 November, 2019 15:27, Jake Thaw, on
>>
>> >Why not like this?
>>
>> >insert into t (a, b, c, d, e, idate)
>> >SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY
>> >idate desc limit 1;
>>
>> Or, if using bound paramaters (and you should be):
>
>What are bound parameters? And where can I read about these? I see
>people use the ? all the time, and I have no idea how that works.
>
>> insert into t (a, b, c, d, e, idate)
>>select ?, b, c, ?, e, ?
>> from t
>> where a = ?1
>> order by idate desc
>> limit 1;
>>
>> then you bind the three parameters a, d, idate.
>
>How do I bind the three parameters?
>
>> Whether you want "order by idate desc" or "order by idate" depends on
>whether you want the newest or oldest record to be the template.
>
>Yep, this I know. And yes, I want the newest, so descending is what I
>want. Thanks.
>
>josé
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
Simon Slavin, on Thursday, November 14, 2019 06:48 PM, wrote... > > On 14 Nov 2019, at 10:27pm, Jake Thaw, on > > > Why not like this? > > > > insert into t (a, b, c, d, e, idate) > > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY > > idate desc limit 1; > > Dammit. I thought I had tried this, and received a syntax error. Now I see > that it was because I missed out a comma. Thanks for the correction. That just lets you know that you are a human. ;-) > Good illustration of why responses should go to the list rather than direct > to the OP. Indeed. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
Keith Medcalf, on Thursday, November 14, 2019 06:44 PM, wrote... > > > On Thursday, 14 November, 2019 15:27, Jake Thaw, on > > >Why not like this? > > >insert into t (a, b, c, d, e, idate) > >SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY > >idate desc limit 1; > > Or, if using bound paramaters (and you should be): What are bound parameters? And where can I read about these? I see people use the ? all the time, and I have no idea how that works. > insert into t (a, b, c, d, e, idate) >select ?, b, c, ?, e, ? > from t > where a = ?1 > order by idate desc > limit 1; > > then you bind the three parameters a, d, idate. How do I bind the three parameters? > Whether you want "order by idate desc" or "order by idate" depends on whether > you want the newest or oldest record to be the template. Yep, this I know. And yes, I want the newest, so descending is what I want. Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
Jake Thaw, on Thursday, November 14, 2019 05:27 PM, wrote... > > Why not like this? > > insert into t (a, b, c, d, e, idate) > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY > idate desc limit 1; Thanks. Yes, this is great! Darn it, I didn't think of this. Thanks again. josé > On Fri, Nov 15, 2019 at 9:19 AM Simon Slavin, on > > > > On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera, on > > > > > insert into t (a, b, c, d, e, idate) values > > > ( > > >(SELECT a FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > > >(SELECT b FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > > >(SELECT c FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > > >'y', > > >(SELECT e FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > > >'2019-02-12' > > > ); > > > > > > Is there a simpler way? Thanks. > > > > No simpler way. I suggest you duplicate the exiting row first, then UPDATE > > the duplicate. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
Simon Slavin, on Thursday, November 14, 2019 05:18 PM, wrote... > > On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera, on > > > insert into t (a, b, c, d, e, idate) values > > ( > >(SELECT a FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >(SELECT b FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >(SELECT c FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >'y', > >(SELECT e FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >'2019-02-12' > > ); > > > > Is there a simpler way? Thanks. > > No simpler way. I suggest you duplicate the exiting row first, then UPDATE > the duplicate. Yes, that was my first idea, but I am trying to do this with a bunch of INSERTs. Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
On 14 Nov 2019, at 10:27pm, Jake Thaw wrote: > Why not like this? > > insert into t (a, b, c, d, e, idate) > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY > idate desc limit 1; Dammit. I thought I had tried this, and received a syntax error. Now I see that it was because I missed out a comma. Thanks for the correction. Good illustration of why responses should go to the list rather than direct to the OP. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
On Thursday, 14 November, 2019 15:27, Jake Thaw wrote: >Why not like this? >insert into t (a, b, c, d, e, idate) >SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY >idate desc limit 1; Or, if using bound paramaters (and you should be): insert into t (a, b, c, d, e, idate) select ?, b, c, ?, e, ? from t where a = ?1 order by idate desc limit 1; then you bind the three parameters a, d, idate. Whether you want "order by idate desc" or "order by idate" depends on whether you want the newest or oldest record to be the template. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
Why not like this? insert into t (a, b, c, d, e, idate) SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY idate desc limit 1; On Fri, Nov 15, 2019 at 9:19 AM Simon Slavin wrote: > > On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera wrote: > > > insert into t (a, b, c, d, e, idate) values > > ( > >(SELECT a FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >(SELECT b FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >(SELECT c FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >'y', > >(SELECT e FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >'2019-02-12' > > ); > > > > Is there a simpler way? Thanks. > > No simpler way. I suggest you duplicate the exiting row first, then UPDATE > the duplicate. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera wrote: > insert into t (a, b, c, d, e, idate) values > ( >(SELECT a FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), >(SELECT b FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), >(SELECT c FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), >'y', >(SELECT e FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), >'2019-02-12' > ); > > Is there a simpler way? Thanks. No simpler way. I suggest you duplicate the exiting row first, then UPDATE the duplicate. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
