Android Question Setting SQL field where two tables match

[Kintara]

Hi, I have two tables Stores and Images.
Stores:
Item
Location
Price
HasImage

Images:
Item
Image (as a blob)

I would like to set Stores.Hasimage to 'Image' if the item number appears in both tables.
Is there a single query that will do this? At the moment I an iterating through all values of Item in Stores and it is time consuming.
Pseudo code:
"UPDATE Stores SET Item WHERE (Stores.Item =Images.Item) "

Many thanks

Kintara

 

[eurojam]

you have to build a join between the two tables, this should be something like this:
"UPDATE STORES INNER JOIN IMAGES ON STORES.ITEM = IMAGES.ITEM SET STORES.HASIMAGE = IMAGES.IMAGE;"
cheers
stefan

PS: nice dog on your avatar, when I was a child we had a boxer too....

 

[Kintara]

Many thanks. I had been playing around with joins but really have no idea what I'm doing until it works. I couldn't get it to work, so I did the wisest thing and asked the forum.
That's my Ruby boxer. Such a lovely girl. She is 5 now and still a lovely as ever.

Kintara

 

[Kintara]

Just tried the suggestion (modified it to the proper Table and field names) but it fails on 'INNER' of 'INNER JOIN'. I don't think android SQLite3 supports 'INNER JOIN'.
I'll state my query again:
The table to be updated is held in the variable 'TableName'. In this case it is "Plant1109"
The database is connected through Main.SQLStoresImages.ExecNonQuery.
In the database:
There is a table called 'Plant1109'. It has a field called 'Item' and another field called 'HasImage'. There are other fields not involved in this.
There is a table called 'Images'. It has a field called 'Item' and another field called 'Pictures' which contains image blobs.
So, I want to SET the field called 'HasImage' in Table 'Plant1109' to the text word 'IMAGE' if the the text value in the field called 'Item' in table 'Plant1109' also appears in the field 'Item' in table 'Images'.

Psudo code:
UPDATE Plant1109 SET HasImage= 'IMAGE' WHERE Plant1109.Item is also in Images.item

I used:

Main.SQLStoresImages.ExecNonQuery("UPDATE " & TableName & " INNER JOIN Images ON " & TableName & ".item = Images.item SET " & TableName & ".HasImage = 'IMAGE';")

but it fails on 'INNER' of 'INNER JOIN'.
Any further ideas welcome.

Kintara

 

[hbruno]

Have you test with "NATURAL JOIN" ?
see :
http://zetcode.com/db/sqlite/joins/
or
https://www.sqlite.org/lang_select.html

 

[Kintara]

I'll try doing it literally:
Main.SQLStoresImages.ExecNonQuery("UPDATE Plant1109 NATURAL INNER JOIN Images ON Plant1109.item = Images.item SET Plant1109.HasImage = 'IMAGE';")

update:
Nope still not getting it.
I don't think UPDATE can have any type of JOIN.
Please correct me if I am wrong on that.

Kinatra

 

[Mahares]

Here is the correct code:

Dim TableName As String ="Plant1109"
txt="UPDATE " & TableName  & " SET HasImage='IMAGE' WHERE Item =  " _
& "(SELECT P.Item FROM " & TableName & " P INNER JOIN Images I ON P.Item = I.Item)"
Main.SQLStoresImages.ExecNonQuery(txt)

 

[Kintara]

Thanks Mahares. I tried that but it only works for the first instance of a match between the two tables not for the whole table.

 

[sorex]

I guess your idea about databases is wrong. You don't need to update that field you just need to check for it when reading that table.

 

[sorex]

I think your idea about databases is wrong. You should not set that field but check for it in the select query.

It will make things easier when removing images aswell.

I'll replicate your database structure and see if I can get it to work with SQLite as I don't know all its limits.

 

[sorex]

here you go

select location,price,(select count(*) from images as i where i.item=plant.item) from plant1109 as plant

you even have the benefit that you get the exact image count here

 

[sorex]

is you just want 0 or 1 as hasimage field you can use this

select location,price,case when (select count(*) from images as i where i.item=plant.item)>0 then 1 else 0 end as hasimage from plant1109 as plant

 

[keirS]

To do what the OP wants.

UPDATE stores SET hasimage = 1 WHERE item in (SELECT item from images)

 

[Troberg]

As Sorex said, you are not doing this "the database way".

I hope this does not come across as a snark, because it's not intended that way, but you'll save yorself a lot of trouble if you grab a book on the subject of databases and learn how to design and use databases properly.

It's also a very valuable skill to have professionally. Almost all larger projects have a database in it somewhere, and the demand for people who are good at databases is much larger than the supply.

What's wrong in your current solution:

You are storing calculated information that could easily and efficiently be obtained on the fly by joining the two tables. Never, ever store calculated information unless there is a very compelling reason to do so. Sooner or later, you'll end up in a situation where things get out of sync, where the calculated field don't match the data it's based on.

Also, you don't seem to have a relation between the tables. This means that the database won't stop you from storing images for non-existing items, or deleting items without deleting their images. You may think that you can stop that from happening in code, but, trust me, it will happen. Tables that are not related to any other tables are usually a clear symptom of a bad database design.

I would do the tables like this:

Stores:
ItemID
Location
Price

Images:
ImageID (You always want a unique ID on each record)
ItemID
Image (as a blob)

Then added a relation between the two ItemIDs.

Then, you can, for example, simply get all images for an item by something like:

select Image from Images join Stores on Images.ItemID = Stores.ItemID where ItemID = ItemIAmLookingFor;

If you don't get any rows returned, there was no images.

Edit: A good "lab environment" to fiddle around with databases is Microsoft Access. It allows you to quickly build tables, set up relations and has a quite powerful query builder which allows you to make complicated SQL statements in no time. I've worked with databases for decades, and still use Access to build my more complicated SQL statements.

 

[Mahares]

Although the suggestions by Sorex and Troberg are very valid, if you want to accomplish what you want right now with your approach, all you had to do is replace the = with IN in my previous post. See below code: If you want a small project I can make you one that uses your method.

txt="UPDATE " & TableName & " SET HasImage='IMAGE' WHERE Item IN " _
& "(SELECT P.Item FROM " & TableName & " P INNER JOIN Images I ON P.Item = I.Item)"
Main.SQLStoresImages.ExecNonQuery(txt)

 

[Kintara]

Wow! thanks everyone. Certainly a few ideas to try out there.
The reason the two tables are not combined is that they cone from different sources, One is a stores holding dump, the other is a collections of images ot the items in the stores that is built up over time and new images added as and when.
In the app, when searching for an item a list is shown in a list view. If the item also has an image its entry is marked with an '*'. I tried to do it on the fly, but if a long list was returned then it took a very long time to check each entry for an image.
So, when a new stores dump is imported into the table, I scan the Plant1109 and Images tables for matching items and mark HasImage in the Plant1109 table.
There are also other Plant1198 and Plantxxxx tables which will hold different items but I still want to match to images. Hench the setting of the HasImage field in each Plant table.

Ill give the suggestions a go and see what happens.

P.S. Each item will only have one image so the Item number (7 digit integer starting with '1' e.g 1690191) will act as the unique ID in each table.

Kintara

 

[Kintara]

Although the suggestions by Sorex and Troberg are very valid, if you want to accomplish what you want right now with your approach, all you had to do is replace the = with IN in my previous post. See below code: If you want a small project I can make you one that uses your method.

txt="UPDATE " & TableName & " SET HasImage='IMAGE' WHERE Item IN " _
& "(SELECT P.Item FROM " & TableName & " P INNER JOIN Images I ON P.Item = I.Item)"
Main.SQLStoresImages.ExecNonQuery(txt)

Thanks Mahares. Your solution did exactly what I was after.
I now have several tables (Plant1109, Plant1198, PlantXXXX) that have the word 'IMAGE' against all the Items that have a picture in the Images table.

e.g
Plant 1109
1690191 MHA23 5 IMAGE
1690192 MHA24 2
1690198 MHA31 8 IMAGE

Plant 1198
1690191 FC55 1 IMAGE
1690192 FC21 2
1690193 FC43 0 IMAGE
1690198 CF77 8 IMAGE

What a great forum.

Kintara

 

[Troberg]

Wow! thanks everyone. Certainly a few ideas to try out there.
The reason the two tables are not combined is that they cone from different sources, One is a stores holding dump, the other is a collections of images ot the items in the stores that is built up over time and new images added as and when.
In the app, when searching for an item a list is shown in a list view. If the item also has an image its entry is marked with an '*'. I tried to do it on the fly, but if a long list was returned then it took a very long time to check each entry for an image.
So, when a new stores dump is imported into the table, I scan the Plant1109 and Images tables for matching items and mark HasImage in the Plant1109 table.
There are also other Plant1198 and Plantxxxx tables which will hold different items but I still want to match to images. Hench the setting of the HasImage field in each Plant table.

Ill give the suggestions a go and see what happens.

P.S. Each item will only have one image so the Item number (7 digit integer starting with '1' e.g 1690191) will act as the unique ID in each table.

Each of these problems are not only easily solvable with relations, they are typical problems databases are used to solve. Try to work with the tool instead of against it. It will be so much easier in the long run.

 

[Kintara]

Thanks for you pointers Troberg.
I only occasionally use SQL so devoting a lot of time to understanding the deeper elements is not something I shall be doing.
I do not really understand how to get a relation between the tables. All the tables are produced from external programs.
They are then imported into the app.
When any of the Plant files (.csv) or image files (a replacement database) are loaded into the database they are cross referenced at that time, the plant tables having the HasImage field filled with Image or left blank.
The Images table probably will have redundant images in it ( that are not listed in any of the Plant Tables, but as it is used across many sites, each with their own Plant tables, I thought it best to keep ALL available images in there.

Anyway, there are some interesting ideas to bear in mind for next time.

Kinata