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'
where
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:
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 your data; in
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.
Thank you for your kind assistance folks
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 concrete example
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
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 sqlite
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 table ORDER BY rowid LIMIT 1;
Thank you, that
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 table ORDER BY rowid DESC LIMIT 1;
Okay, I feel very stupid now, hadn't twigged. I
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 ?,? represent
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:
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 amp; 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
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
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
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
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 the category when it is the same as the last
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
select * from mytable t1 where exists (
select * from mytable t2
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
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
31 matches
Mail list logo