How to sqlite3 SELECT by COLUMN value

0
votes

My SQL db has 2 tables, uidmap and gidmap each having 2 columns (UID,SID) and (GID,SID) respectively

Both the tables have single entry, as of now:

GID|SID 30000000|S-1-22-2

UID|SID 30000000|S-1-22-2

Here UID/GID are the primary key and SID also has UNIQUE constraint

I need to add entry into the tables , but first need to check if any such entry already exists The problem I have that in a for loop, I need to do SELECT from the table where UID=<some-value> or GID=<some-value>

One way of doing is to first match which table we are querying into then match UID or GID

Is there a way to match the COLUMN values directly instead of names ?

Something of the type: for table in TABLES: SELECT * FROM table WHERE '%ID'=<some-value> and SID=<some-value>

Also have one additional query, whey do following fail:

SELECT * from gidmap where UID=<some-value> Error: no such column: UID

While a "" around UID suppresses the no such column error:

SELECT * from gidmap where "UID"=30000000

2
sqlite
Do the UIDs and GIDs have distinct values? – CL.
Nope, they are they are same values, as of now. But could be different too – mittal
Then how would such a query make sense? Show some example data and the desired result. – CL.
I have updated the question with sample data and more precise problem statement – mittal
I also posted a question in the end, why does "" around UID suppresses the error ? – mittal

2 Answers

0
votes

In this case, it is not possible to refer to a column value without mentioning the colum name.

Anyway, to prevent errors when you're trying to insert the same values again, and if you do not need to update any old rows, you can simply use INSERT OR IGNORE.

0
votes

One way to simplify this issue, is to use upsert. With it, you don't need to check if you're inserting something that already exists, since this feature will simply update it (or do something innocuous). While this might not be the most efficient approach, it eliminates the problem of having to check if the data already exist before inserting it.