>update locations
> set ItemCount='1'
> where exists(
> select Location from hive where locations.Location=hive.Location
> )
Okay, seems that I wasn't too far off. Thank you very much for the help, I
certainly needed it:):)
>update locations
> set ItemCount='0'
>
>You can do both in one pass:
>update locations set ItemCount =
>case when Location in (select Location from hive)
> then '1' else '0'
>end;
Thanks for your help Igor, extremely elegant solution.
--
View this message in context:
MySql and msSql have seek commands to go to a( or return) a specific row in a
database base don the internal row number rather than an id or other
identifier.
Is there a generic SELECT statement that can do this for sqlite or indeed,
any other sql variant?
Regards.
--
View this message in
Apologies for not being too clear. Normally, I'd just query a database based
on "where" criteria to get results, but that is based on a continually
expanding and dynamically changing database.
In this case, I have a static (book records) database and I want to select a
specific row based on the
Is there a way of querying the database to list all duplicate entries from a
column in the same table?
Something like "SELECT * FROM mytable WHERE last NOT UNIQUE"
fred, johnson
roger, johnson
--
View this message in context:
Igor Tandetnik wrote:
>
> flakpit <[EMAIL PROTECTED]> wrote:
>> Is there a way of querying the database to list all duplicate entries
>> from a column in the same table?
>>
>> Something like "SELECT * FROM mytable WHERE last NOT UNIQUE"
>
>
Hello, due to the wonderful help that I have recieved before, I dare to ask
"I have records in a database with Category and Subcategory fields. How
would I formulate the query to return distinct results and return for
whatever I need to do with them?"
There may be thousands of category and
Dennis Cote wrote:
>
> flakpit wrote:
>
> You could try
>
>select distinct category, subcategory
>from documents
>order by category, subcategory;
>
> Then your application can do the display formatting, such as suppressing
> the display of
I use a language called PureBasic and am playing with BLOB data. Got data
saving successfully but read in these forums that updating is the same. Yes,
I know it's a compiled statement, saving to database is fine, I can even
view my saved blobs so I know my code works..
The SQL to insert the blob
This is the way I normally create my shopping database, but this leads to
thousands of duplicates.
CREATE TABLE shopping(item TEXT,units TEXT,quantity TEXT,category TEXT,shop
TEXT,aisle TEXT,price TEXT,total TEXT,date TEXT,note TEXT,record INTEGER
PRIMARY KEY AUTOINCREMENT)
'item' is the full
Thank you Igor, the solution below (that you also posted) is what I found in
the forums after hours of searching when I should have been asleep. And
rather that produce shoddy code, I downloaded a proper sqlite tool to verify
that it was all working fine. Thank you for the response, I have
I understand your solution Woody, but it isn't practical for me, not for my
home shopping list program. A full on relational database like that is an
awful lot of work and it's only for home use (and any other family I can con
into using it and testing it).
I'd go for the relational route if I
Harold Wood Meyuni Gani wrote:
>
> U, hmm. The tips I gave you were from my pda based shopping program
> that will be selling for 9.99 soon.
>
Good on you Woody, hope you sell a lot.! If my eyes were up to the challenge
of reading my PDA's small screen, i'd buy a copy and save myself
I've been escaping single quote characters in all my text fields and using
the sqlite_execute function to put the data into the table. But is it
possible to use the prepare/bind commands to enter data so that I don't have
to do this?
I've been using the column_text type to get back a block of
SQLite doesn't truncate anything. Either you truncated at the time you
> put the data into the database in the first place, or you are truncating
> now when looking at the string.
It definately wasn't truncated when I put it in, I checked. So as you say,
something is truncating it as it is
Peter Holmes-4 wrote:
>
> Yep. Works great! For example:
>
> sqlite3_prepare_v2(db,"INSERT INTO ans VALUES (?,?);",-1,stmt,NULL);
> sqlite3_bind_text(stmt,0,"Peter's",-1,SQLITE_STATIC);
> sqlite3_bind_text(stmt,1,"Reply",-1,SQLITE_STATIC);
> sqlite3_step(stmt);
> sqlite3_reset(stmt);
>
so
Enrique Ramirez-3 wrote:
>
> Depends on where you're looking at your block of text. Are you using a
> GUI SQLite Manager of sorts, or maybe peeking at the variable's
> contents from a dev IDE?
>
I checked the contents of the db to ensure that all my text was in it and it
was. Then I peek the
Dennis Cote wrote:
>
> See, http://www.sqlite.org/c3ref/prepare.html for details on prepare and
> previous link for detail on bind_text.
> Dennis Cote
>
Thank you for the links Dennis, this will help me a lot.
--
View this message in context:
Good evening.
Due to many deletions, my database is non sequential anymore.
Is there any way to fetch the very first record in the database and find the
id number? Or for that matter, a function to select the last record?
I've tried the "SELECT TOP 1" function butt that apparently isn't
Dan Kennedy-4 wrote:
>
>
>> Is there any way to fetch the very first record in the database and
>> find the > id number?
>
> The 'first record' is a malleable concept. You can find the record with
> the lowest rowid value with:
>
>SELECT ... FROM ORDER BY rowid LIMIT 1;
>
>
Thank
> 2. The above statement gets the lowest rowid but there appears to be
> no
> implicit statement to the direction of the ORDER BY clause. Can the
> statement be reversed to get the highest rowid?
> SELECT ... FROM ORDER BY rowid DESC LIMIT 1;
Okay, I feel very stupid now, hadn't twigged. I
Currently, I return any needed data like this.
select * from pubs,notes,publishers where pub_title like '%salem%'
and pubs.note_id=notes.note_id
and pubs.publisher_id=publishers.publisher_id
And it works except for all fields in the matching tables being returned.
Is there any way using
>SELECT pubs.* from pubs, notes, publishers WHERE ...
Thanks, but it didn't work the way I expected it to:)
Now I have my query laid out a lot better and can 'slim' it down from here
SELECT * FROM pubs
INNER JOIN notes
ON pubs.note_id=notes.note_id ; notes.
INNER JOIN publishers
ON
If anyone else has a moment and a concrete example instead of sending me to
tutorials, feel free to step in here.
SELECT * FROM pubs; I want ALL columns from the 'pubs'
table
WHERE pub_title LIKE '%salem%' ; Where the title sounds like 'salem'
In the pubs table, there is
> You did. You should still do some SQL tutorials.
>In the meantime, I hope the above helps.
Adjusted the fieldnames accordingly and your example worked perfectly. Thank
you.
However, repeating that I should do some SQL tutorials is annoying in
extremis. I had been to many SQL tutorials and
On 18/04/2009 2:33 PM, flakpit wrote:
> If anyone else has a moment and a concrete example instead of sending me
> to
> tutorials, feel free to step in here.
>If you would take a moment to read the fraction of a screen of a
>tutorial that I pointed you at (and which contains a c
To to get columns that begin with a number range or any number, I would use
in SQL server:
SELECT * FROM mytable WHERE myfield LIKE '[0-9]%'
This obviously doesn't work in sqlite and I have been searching for an
equivalence.
--
View this message in context:
flakpit wrote:
>
> To to get columns that begin with a number range or any number, I would
> use in SQL server:
>
> SELECT * FROM mytable WHERE myfield LIKE '[0-9]%'
>
> This obviously doesn't work in sqlite and I have been searching for an
> equivalence.
>
Hello folks, sorry to bother you. You must admit I take 6 months or more to
ask questions:):)
Using the first part of this query works fine to return data by searching
the multiple concatenated columns, very happy with that.
SELECT * FROM sitelist
WHERE
>Just add your AND and ORDER by clauses on after the '%wik%'. But I don't
know why there's a '7' at >the end of your original line. I don't think it
does anything. Perhaps a typo when you made the post ?
>SELECT * FROM sitelist
>WHERE
Igor Tandetnik wrote:
>
>
> Define "didn't work". What results did this query return, and how do these
> results differ from your expectations?
>
>> it is not giving me any results at the moment.
>
> Well, do you actually have any records that match all the conditions? Show
> a sample of
31 matches
Mail list logo