Update TAG Relational table from select match

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Update TAG Relational table from select match

Author

Topic

Mchael
Starting Member

3 Posts

Posted - 2011-07-05 : 08:05:44

Hello,

I imported the Software PAD File Database from http://paddatabase.net/download.html into Microsoft Access in a table called main:

MAIN
-----
ID
ProgramName
Keywords
.
.

I created two new tables: Tags and TagSoftwareRel.

Tags
--------
ID
Tag

TagSoftwareRel
--------------
ID
SoftwareID <- (MainTable)
TagID <- tags table

I extracted all the keywords from the field Keywords as individual words in the Tags table. They Keywords field from Main looks like this:

Keywords
Paul, animated, moving monk, paulaner
Image,Imaging,Graphics,Rotate,Resize,Effects,
Sharpen,Blur,JPEG,TIFF,BMP,WBMP,PSD,GIF,PDF,Format,ICM,ICC,CMYK,
thumbnail,Convert,Display,AJAX,AVI,red-eye removal,lossless JPEG transform
correction, rich,internet,applications,ebooks,webmaster,authoring,

What I want to do is create a SQL Query which Inserts the tagID from the tags table into tagsoftwarerel.tagid and the related softwareID from the main table into tagsoftwarerel.softwareid by using a where tags.tag like main.keywords

I'm sort of at loss of where to start with this.

As it is a public DB I can provide a copy of the database to anyone interested.

Any assistance would be most appreciated. Thank you.

-----
Expert Windows Hosting Since 1997 - M6.Net

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-05 : 08:37:43

insert TagSoftwareRel (SoftwareID , TagID )
select m.ID, t.ID
from MAIN m
join Tags t
on ',' + m.Keywords + ',' like '%,' + t.Tag + ',%'

You have sime spaces in the keywords in the main table which might be an issue.
Another option is to parse the keywords from main into a temp table with id, keyword then join to that - depends on how many you have and how much time.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mchael
Starting Member

3 Posts

Posted - 2011-07-05 : 08:48:30

THanks Nigel.

I think I have put this in the wrong forum as I am using Microsoft Access. I tried the query example you gave and I get a syntax error. I tried to fix the error in Access as best I could with:

insert into TagSoftwareRel (SoftwareID , TagID )
select main.ID, tags.ID
from MAIN m
join Tags t
on ',' + main.Keywords + ',' like '%,' + tags.Tag + ',%'

I am not understanding the m part of from Main m and Join tags t - specifically the m and the t and what they relate

Thank you for your assistance.

-----
Expert Windows Hosting Since 1997 - M6.Net

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-05 : 09:41:27

They are just aliases for the table names.
You need to take them out if you want to use the full tablename.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elenalee
Starting Member

2 Posts

Posted - 2011-07-27 : 01:45:35

thanks for the nice information about MS Access
================
unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources