Re: [sqlite] How to fix Syntax - Check exists

[P Kishor]

On Sat, Apr 17, 2010 at 12:10 AM, gretty  wrote:
>
> Hello
>
> I am a programmer using SQLite3 to create a database for an application. I
> have been running into some problems with my SQL queries which I dont think
> are correct. Can you check them & correct them where relevant?
>
> Right now only one table exists, its layout is like so:
>  [quote]
> Table = element
> 2 columns = property TEXT PRIMARY KEY, value TEXT
>
> ie, CREATE TABLE element(property TEXT PRIMARY KEY, value TEXT)[/quote]
>
> Are these queries correct?
>
> Check if property already exists in table:
> [code]
> SELECT property
> FROM element
> WHERE property == 'color';[/code]
>

The correct query would be

SELECT property FROM element WHERE property = 'color';

However, you already know the property = 'color', so why SELECT it?

> If I get NULL back that will mean that this property does not exist in the
> table? Is this correct?
>

Well, no. If you get no rows back then it means that property =
'color' doesn't exist. NULL would mean that the property does exist
but has no value.

> Change the value in a row where property equals color:
> [code]
> DELETE value
> FROM element
> WHERE property == 'color';
>
> INSERT INTO element(value)
> VALUE('orange')
> WHERE property == 'color';
>

What you really want is

  UPDATE element SET value = 'orange' WHERE property = 'color';

I suggest you go through any of the many SQL tutorials you can find via Google.

> commit;[/code]
>
> Also I want to create a new TABLE with the name '#element' or '.element' but
> I always get an error because of the '#' & '.' characters. Is it possible to
> create a table with a '#' or '.' character in its name?

Yes. Quote the names with double quotes.


> --
> View this message in context: 
> http://old.nabble.com/How-to-fix-Syntax---Check-exists-tp28274195p28274195.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to fix Syntax - Check exists

[gretty]

Hello

I am a programmer using SQLite3 to create a database for an application. I
have been running into some problems with my SQL queries which I dont think
are correct. Can you check them & correct them where relevant?

Right now only one table exists, its layout is like so:
 [quote]
Table = element
2 columns = property TEXT PRIMARY KEY, value TEXT 

ie, CREATE TABLE element(property TEXT PRIMARY KEY, value TEXT)[/quote]

Are these queries correct?

Check if property already exists in table:
[code]
SELECT property
FROM element
WHERE property == 'color';[/code]

If I get NULL back that will mean that this property does not exist in the
table? Is this correct?

Change the value in a row where property equals color:
[code]
DELETE value
FROM element
WHERE property == 'color'; 

INSERT INTO element(value)
VALUE('orange')
WHERE property == 'color';

commit;[/code]

Also I want to create a new TABLE with the name '#element' or '.element' but
I always get an error because of the '#' & '.' characters. Is it possible to
create a table with a '#' or '.' character in its name?
-- 
View this message in context: 
http://old.nabble.com/How-to-fix-Syntax---Check-exists-tp28274195p28274195.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users