Adding a record to a table with one value change

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Adding a record to a table with one value change

Jose Isaias Cabrera-4

Greetings!

I have this table,

create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, '2019-02-11');
select * from t;
1|p001|1|2|n|4|2019-02-11
2|p002|2|2|n|4|2019-02-11
3|p003|3|2|n|4|2019-02-11
4|p004|4|2|y|4|2019-02-11
5|p005|5|2|y|4|2019-02-11

and I want to add a new record based on the p001 record.  I only want to change two values, d and idate.  I can do this with this command,

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.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Simon Slavin-3
On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Jake
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 <[hidden email]> wrote:

>
> On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Keith Medcalf

On Thursday, 14 November, 2019 15:27, Jake Thaw <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Simon Slavin-3
In reply to this post by Jake
On 14 Nov 2019, at 10:27pm, Jake Thaw <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Jose Isaias Cabrera-4
In reply to this post by Simon Slavin-3

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Jose Isaias Cabrera-4
In reply to this post by Jake

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Jose Isaias Cabrera-4
In reply to this post by Keith Medcalf

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Jose Isaias Cabrera-4
In reply to this post by Simon Slavin-3

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Keith Medcalf
In reply to this post by Jose Isaias Cabrera-4

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 <[hidden email]> On
>Behalf Of Jose Isaias Cabrera
>Sent: Friday, 15 November, 2019 06:20
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a record to a table with one value change

Jose Isaias Cabrera-4


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users