>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:
http://old.nabble.com/Update-one-table-from-matches-in-another-t
>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'
>
To see what matches the location table with locations in the inventory table,
I can issue the following command and it works returning 17 locations
matched and is correct. There are 21 locations in the locations table but
only 17 used in the inventory table so I know the query is returning correct
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 you
>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
sitename||username||password||serialnum||misc1||misc2||misc
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
sitename||username||password||serialnum||
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
> e
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:
http://old.nabble.com/Searching-for-
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
> 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 alwa
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 an
>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 pubs.publi
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
> 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
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 y
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 sqlit
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:
http://www.nabble.com/SQLITE_prepare-bind-to-exn
Peter Holmes-4 wrote:
>
> Make that:
>
> sqlite3_prepare_v2(db,"INSERT INTO ans VALUES (?,?);",-1,&stmt,NULL);
>
Peter, sorry to be a nuisance but, can you update the same way? I've not
idea how that statement would look with this way of doing things
--
View this message in context:
http
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 r
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 ?
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 bei
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 tex
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 wor
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 we
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 learned
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 r
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
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 th
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 subcat
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"
>
>
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:
http://www.nabble.com/Listing-duplicate-entries-tp16941525p1694152
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 p
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 conte
33 matches
Mail list logo
