[sqlite] how can we solve IF EXIST in SQLite

hi am new to SQLite can anybody please tell me how this query can be solved in SQLite? IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE prod_batch_code=1000) UPDATE stock_tab SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab WHERE oduct_batch_code=1000 )

Re: [sqlite] how can we solve IF EXIST in SQLite

Hi, what language is this? it certainly is not SQL or a "query". I suspect that you can not use "insert or replace" (see http://www.sqlite.org/lang_insert.html), because you look first for a record with prod_batch_code=1000, and if you do not find it you insert one with prod_batch_code = 1003.

Re: [sqlite] how can we solve IF EXIST in SQLite

hey thanx for the reply... u leave the things happening inside.. wat i jus wanna do is i wanna insert a new row to a table the table will be like this stock_id PKproduct_id FK quantitystock_date 1 10001028-05-2009 10001

Re: [sqlite] how can we solve IF EXIST in SQLite

Hi, as far as I know, you cannot do what you want to do in pure SQL. However, perhaps someone cleverer can contradict me. You could first execute the update statement, check if there was a row which was updated using sqlite3_changes() (see http://www.sqlite.org/capi3ref.html#sqlite3_changes),

Re: [sqlite] how can we solve IF EXIST in SQLite

Hello, you are cleverer than you think. Your initial idea to use INSERT OR REPLACE might look like: INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date) SELECT s.stock_id, p.prod_batch_code, IF_NULL (s.stock_qty, 0) + p.purchase_qty

Re: [sqlite] how can we solve IF EXIST in SQLite

On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew wrote: > >hey thanx for the reply... u leave the things happening inside.. wat i jus >wanna do is i wanna insert a new row to a table >the table will be like this >stock_id PKproduct_id FK quantity

Re: [sqlite] how can we solve IF EXIST in SQLite

Sorry, this was written down without testing. I see now that prod_batch_code must be the primary key, instead of stock_id, for the REPLACE to work as expected. Then some other expression must be used to fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this message crosses Kees

Re: [sqlite] how can we solve IF EXIST in SQLite

thanx for ur reply dude.. but its showing an error " no such function: NULL_IF" Edzard Pasma wrote: > > Hello, you are cleverer than you think. Your initial idea to use INSERT OR > REPLACE might look like: > > INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty, > stock_date)

[sqlite] Syntax to set the conflict resolution of a transaction

I'm trying to set the conflict resolution of an entire transaction by using the ON CONFLICT clause without success. I'm using the following syntax: BEGIN ON CONFLICT ROLLBACK; INSERT INTO TableX (Id) Values (1); INSERT INTO TableX (Id) Values (2); INSERT INTO TableX (Id) Values (3); COMMIT; But

Re: [sqlite] how can we solve IF EXIST in SQLite

guys i ll clarify the problem this is the purchase table here purchase id is PK purchase_id prod_batch_code vendor_type_code purchase_qty purchase_date --- --- --- 11000 1 100

Re: [sqlite] Syntax to set the conflict resolution of a transaction

On Tue, 2 Jun 2009 08:40:01 -0300, Karl Brandt wrote: >I'm trying to set the conflict resolution of an entire transaction by >using the ON CONFLICT clause without success. > >I'm using the following syntax: > >BEGIN ON CONFLICT ROLLBACK; >INSERT INTO TableX (Id) Values (1);

Re: [sqlite] Syntax to set the conflict resolution of a transaction

On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt wrote: > I'm trying to set the conflict resolution of an entire transaction by > using the ON CONFLICT clause without success. > > [...] > > Is there a way to set the conflict resolution for an entire transaction? Such a

Re: [sqlite] how can we solve IF EXIST in SQLite

If you have unique index on stock_tab.prod_batch_code then you can re-write your trigger as this: INSERT OR REPLACE INTO stock_tab (stock_id, prod_batch_code, stock_qty, stock_date) SELECT new.purchase_id+2, new.prod_batch_code, new.purchase_qty +

[sqlite] Why does this sql error

I use the following sql INSERT INTO malware (file, location, md5, size, sig, sig_name, cnt, clam_result, date_found, date_removed, ref) VALUES ('Setup.exe-IRAD0n', '/Users/tshaw/malware/Setup.exe-IRAD0n', '1186b3a97de73f924dcfb12cba0bb1bf', 15360, '', '', 1,

Re: [sqlite] Syntax to set the conflict resolution of a transaction

2009/6/2 J. King > > On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt > wrote: > > > I'm trying to set the conflict resolution of an entire transaction by > > using the ON CONFLICT clause without success. > > > > [...] > > > > Is there a way to set the

Re: [sqlite] how can we solve IF EXIST in SQLite

If prod_batch_code is not a unique key (which is surprising as you may be updating more than one row), we can still write a pseudo INSERT OR REPLACE in the form of both an update and an insert statement. The update can go unchanged. The insert should not use values () but a query that only

Re: [sqlite] how can we solve IF EXIST in SQLite

thanx a lot dude Pavel Ivanov-2 wrote: > > If you have unique index on stock_tab.prod_batch_code then you can > re-write your trigger as this: > > INSERT OR REPLACE INTO stock_tab > (stock_id, prod_batch_code, stock_qty, stock_date) > SELECT new.purchase_id+2,

[sqlite] sqlite programmed in C++

Hello, I would like to know if someone already though about to introduce C++ in SQLite? I just think about a minimal subset of C++ that will not make any performance penalty (like C with classes) is it a performance issue? is it a deployment/compiler issue? or any issue? Please don't make any

Re: [sqlite] sqlite programmed in C++

I have used sqlite for an embedded application using c++. The sqlite library is all in c and compiles on its own. I then created several classes that use the sqlite api. The first is a DbHandler class. This opens the database and provides the public accessors to the rest of application. I

Re: [sqlite] sqlite programmed in C++

2009/6/2 Sylvain Pointeau : > Hello, > I would like to know if someone already though about to introduce C++ in > SQLite? > I just think about a minimal subset of C++ that will not make any > performance penalty > (like C with classes) Prob good idea to look through

Re: [sqlite] sqlite programmed in C++

One of the best and worst things about C++ is its ability to use C code (almost) transparently. Best because it allows you to use things like SQLite seamlessly in your C++ code. Worst because it allows some programming idioms that are in opposition to good object oriented design. But let's

Re: [sqlite] Syntax to set the conflict resolution of a transaction

On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt wrote: >2009/6/2 J. King >> >> On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt >> wrote: >> >> > I'm trying to set the conflict resolution of an entire transaction by >> > using the ON

Re: [sqlite] sqlite programmed in C++

I didn't mean to program with sqlite in C++ ... I just meant to introduce C++ inside SQLite instead of C... I would like to understand why SQLite is programmed only in C Cheers, Sylvain On Tue, Jun 2, 2009 at 5:15 PM, wrote: > One of the best and worst things

Re: [sqlite] sqlite programmed in C++

On Tue, Jun 2, 2009 at 10:33 AM, Sylvain Pointeau wrote: > I didn't mean to program with sqlite in C++ ... > I just meant to introduce C++ inside SQLite instead of C... > I would like to understand why SQLite is programmed only in C Definitively only DRH can

Re: [sqlite] help,low RAM problem

I have used sqlite on a similar platform. I use the GHS Integrity operating system. With a few tweaks I was able to get it to run well. The flash will keep things on the slower side, but I see my inserts (with idexes) taking a little as 0.05 seconds. Queries of 250 out of 10K sorted taking

Re: [sqlite] sqlite programmed in C++

I figure the only reasons it is written in C is for portability and Assembler programming is a bitch! -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of P Kishor Sent: Tuesday, June 02, 2009 10:36 AM To: General Discussion of

Re: [sqlite] Syntax to set the conflict resolution of a transaction

2009/6/2 Kees Nuyt : > On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt > wrote: >> >>Let me explain the complete picture so someone can help me. >> >>I develop a wrapper around sqlite that tracks the changed records and >>than save the changes to the database

Re: [sqlite] sqlite programmed in C++

Because there are many platforms that sqlite runs (and can run at some time) that doesn't have a C++ compiler available, but they always have a C compiler. []'s On Tue, Jun 2, 2009 at 12:33, Sylvain Pointeau wrote: > I didn't mean to program with sqlite in C++ ... >

Re: [sqlite] Types for strings, non-expert question

On Mon, Jun 01, 2009 at 08:56:57PM -0700, Roger Binns wrote: > Dennis Cote wrote: > > Do you have a list of such changes that should be implemented in the > > next breaking release of SQLite? > > I assume you are talking about a major release (ie SQLite v4 not 3.7). > > > I'm thinking of things

Re: [sqlite] sqlite programmed in C++

Why wuld you want to do such a thing? Sylvain Pointeau wrote: > Hello, > I would like to know if someone already though about to introduce C++ in > SQLite? > I just think about a minimal subset of C++ that will not make any > performance penalty > (like C with classes) > > is it a performance

[sqlite] sqlite, MPI and PostgreSQL

Hello, I'm very new user about SQLite. I'm working with Structural Bioinformatics and everybody knows that this area requires a lot of computational resources. So, I'm developing an Evolutionary Algorithms (EA) for some protein analysis (I don't know what will do exactly, because I've just

[sqlite] Before Update trigger question

Hello Gang, This is my first question on this mail list. I noticed that BEFORE UPDATE trigger goes off even for insert statements. My example: CREATE TRIGGER validate_players_update BEFORE UPDATE ON players WHEN (new.role in (1,2) and (select count(*) from players where

Re: [sqlite] sqlite, MPI and PostgreSQL

On 2 Jun 2009, at 5:33pm, Rodrigo Faccioli wrote: > mpi Which of the many things called 'mpi' are you talking about ? A URL will be good. > Is it possible to employ sqlite in mpi application? Example: I have a > computer which is a dual-core and my program works with mpi. Its > goal is to

Re: [sqlite] sqlite, MPI and PostgreSQL

Hello Rogrigo, I'll take a shot at a response to this, bearing in mind there are quite a few salient details you haven't yet provided in this post. --As a principle, I'd recommend keeping your solutions architecture as simple as possible, and use either PostgreSQL or SQLite, but not both,

[sqlite] FTS3

Some further question regarding FTS3. Am I correct that a doclist of a certain term is never split onto two blocks (BLOBs)? Can we somehow limit the size of such BLOBs? I did some tests where I inserted Millions of addresses into FTS3 and all contained a certain term. I ended up with some Blobs

Re: [sqlite] Before Update trigger question

Boris Ioffe wrote: > This is my first question on this mail list. I noticed that BEFORE > UPDATE trigger goes off even for insert statements. My crystal ball shows you have an INSERT trigger that performs an UPDATE. Igor Tandetnik

Re: [sqlite] Before Update trigger question

On Tue, 2 Jun 2009 09:38:18 -0700 (PDT), Boris Ioffe wrote: > >Hello Gang, >This is my first question on this mail list. I noticed that BEFORE UPDATE >trigger goes off even for insert statements. > >My example: >CREATE TRIGGER validate_players_update BEFORE UPDATE ON

Re: [sqlite] Before Update trigger question

Very intrigued. You ruined some sql foundational priciples I lived upon. How is it possible for insert statement to update? Even replace is (delete on constraint then insert). Can you elaborate your answer a bit ? THanks, Boris --- On Tue, 6/2/09, Igor Tandetnik

Re: [sqlite] Before Update trigger question

Boris Ioffe wrote: > --- On Tue, 6/2/09, Igor Tandetnik > wrote: > >> From: Igor Tandetnik >> Subject: Re: [sqlite] Before Update trigger question >> To: sqlite-users@sqlite.org >> Date: Tuesday, June 2, 2009, 2:11 PM >> Boris Ioffe

Re: [sqlite] Before Update trigger question

Kees, Thank you very much for quick prototype. I will use single quotes from now on. It turns out Igor was right. I had another trigger CREATE TRIGGER insert_players_timeStamp AFTER INSERT ON players BEGIN UPDATE players SET create_ts = DATETIME('NOW', 'localtime') WHERE rowid =

Re: [sqlite] Before Update trigger question

Thanks for asking your crystal ball. it works rather well. --- On Tue, 6/2/09, Igor Tandetnik wrote: > From: Igor Tandetnik > Subject: Re: [sqlite] Before Update trigger question > To: sqlite-users@sqlite.org > Date: Tuesday, June 2, 2009, 2:48 PM >

Re: [sqlite] sqlite programmed in C++

... because I experienced C++ to be easier with the classes and resource management via the destructor.I was just wondering why C++ is not used? was it for a performance issue? or a compiler issue? or anything else? I just read the Linus Torvalds comment on the C++ for Git What do you think?

Re: [sqlite] sqlite programmed in C++

On Tue, 2 Jun 2009 21:30:51 +0200, Sylvain Pointeau wrote: >... because I experienced C++ to be easier with the classes and resource >management via the destructor.I was just wondering why C++ is not used? > >was it for a performance issue? >or a compiler issue? What

Re: [sqlite] Before Update trigger question

On Tue, 2 Jun 2009 12:28:31 -0700 (PDT), Boris Ioffe wrote: > > Kees, > Thank you very much for quick prototype. I will use single > quotes from now on. It turns out Igor was right. > I had another trigger. Yes, always read Igor's replies first ;) Triggers are very

Re: [sqlite] sqlite programmed in C++

I think you have to factor the age of SQLite into that explanation as well. I think the first versions of SQLite were released about 10 years ago, at which point C++ compilers were even more non-standard than they are today. Then, once it's functional and stable in C, why rewrite it? On Tue,

[sqlite] Db design question (so. like a tree)

Hi, I am planning a database for animal breeding. I need to store the relations between individuals and therefore I have to build something like a tree structure. But of course with two parents (There wont be cloned animals in the database .-) afaik) I read a little bit about - adjacency

Re: [sqlite] Db design question (so. like a tree)

On Tue, Jun 02, 2009 at 11:16:20PM +0200, Jan scratched on the wall: > Hi, > > I am planning a database for animal breeding. I need to store the > relations between individuals and therefore I have to build something > like a tree structure. But of course with two parents (There wont be >

Re: [sqlite] Db design question (so. like a tree)

>> - adjacency list (not very difficult to understand) > > Also easy to work with two parents, just have a "father" column and a > "mother" column. > > Adjacency lists are quick to update, but many queries can't be done > in (standard) SQL by itself. That usually isn't a problem, and

Re: [sqlite] sqlite programmed in C++

A good tool ltries to be "bette"r, not "easier". Libraries compiled from C are compatible with C++. In general optimization is better with C compilers and the support libraries are more compact and effcient. Producing Sqlite in well written and documented ANSI C was a very sound decision.

Re: [sqlite] Db design question (so. like a tree)

On Tue, Jun 2, 2009 at 10:45 PM, Jan wrote: > Sounds good. I think I try that. Although updating is usually not > necessary (once you have a mother/father its usually difficult to get > rid of/update them .-) I read that there is problem with queries that go > deeper in

Re: [sqlite] Db design question (so. like a tree)

Something to investigate is to use an AVL tree structure with rowids as the pointers. It would stay balanced and you could present family trees quite simply as well as use SQL to extract data on individuals and sets of individuals. Jay A. Kreibich wrote: > On Tue, Jun 02, 2009 at 11:16:20PM

Re: [sqlite] Db design question (so. like a tree)

Hi, > If you don't want to update, but you do want to query for entire > subtrees, do give nested sets more consideration. But as Jay pointed out: Nested sets only work with one parent. Do they? > > The best encoding for intervals I've yet seen is here: > http://arxiv.org/pdf/0806.3115v1 > >

Re: [sqlite] Db design question (so. like a tree)

Hi, thanks. I'll investigate this possibility. John Stanton schrieb: > Something to investigate is to use an AVL tree structure with rowids as > the pointers. It would stay balanced and you could present family trees > quite simply as well as use SQL to extract data on individuals and sets >

Re: [sqlite] UPDATE TRIGGER works in all records

Am Montag, den 01.06.2009, 07:38 -0400 schrieb Igor Tandetnik: > Oliver Peters wrote: > > After an UPDATE in a record I want the update time stored in a column > > of this record - the problem is that the trigger I use doesn't work > > only in this record but in all others > > > > CREATE TRIGGER

Re: [sqlite] Getting last inserted rowid?

"Igor Tandetnik" writes: > Nikolaus Rath wrote: >> How can I determine the rowid of the last insert if I am accessing the >> db from different threads? If I understand correctly, >> last_insert_rowid() won't work reliably in this case. > > Last inserted

[sqlite] UPDATE while SELECT is active

Hello, I am pretty sure that at some point I have read if the following code might bring me into trouble: cur1 = conn.get_cursor() cur2 = conn.get_cursor() res = cur1.execute("SELECT id FROM data WHERE enabled == 1") for name in res: cur2.execute("UPDATE data SET enabled = 0 WHERE id=?",

Re: [sqlite] Getting last inserted rowid?

Nuno Lucas writes: > On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: >> Hello, >> >> How can I determine the rowid of the last insert if I am accessing the >> db from different threads? If I understand correctly, >> last_insert_rowid() won't work

Re: [sqlite] Getting last inserted rowid?

Nikolaus Rath wrote: > "Igor Tandetnik" writes: >> Nikolaus Rath wrote: >>> How can I determine the rowid of the last insert if I am accessing >>> the db from different threads? If I understand correctly, >>> last_insert_rowid() won't work reliably in this

[sqlite] Perticular Field encription in sqlite3 database

Hi, I want to encrypt a perticular field in sqlite3 database. For Example I want encrypt a password field in a table . Is there any special commands or technics for this encryption. Please let me know is there any solution for this . Thanks and Regards Samba DISCLAIMER == This