SQLITE fields

keithrus

If a tables 'name' field is the same as one of the tables 'field' in SQLITE should there be any duplication/problems etc??

I have a table named sb_Market.
One of this tables name is called 'Handicap'
One of the fields' is also called 'Handicap' with a value.

When I make any changes within the 'Handicap' , the name changes to the field value.

ChRoMe

keithrus wrote: »

If a tables 'name' field is the same as one of the tables 'field' in SQLITE should there be any duplication/problems etc??

I have a table named sb_Market.
One of this tables name is called 'Handicap'
One of the fields' is also called 'Handicap' with a value.

When I make any changes within the 'Handicap' , the name changes to the field value.

It should be fine, can you post what query you are using?

Giblet

Are you able to use the fully qualified name? dbname.tablename.column ?

keithrus

SELECT * FROM sb_Market WHERE sb_Market.MeetngId=19798 AND sb_Market.EventId=2 AND sb_Market.Name='Handicap';

FSL

Do you not need [] around Name as Name is an attribute and you are also using it as a field name e.g sb_Market.[Name]='Handicap'.

This is the case in MSSQL.

amen

more fundamental why do you have a table and column with the same name?

Would the table handicap be better named as playerhandicap, personhandicap etc?

keithrus

Resolve found.

Its how SQLITE handles and recognises double and single quotes on an update.

Apparently, this is done on an update to the DB
UPDATE sb_Market SET Name=”Handicap” WHERE...

This works fine, unless there happens to be a field in the table called “Handicap”, in which case the value of this field rather than the text “Handicap” are inserted.

The correct update statement should look like this:



UPDATE sb_Market SET Name=’Handicap’ WHERE...