Assuming template=name you might try something like this: INSERT INTO NameTran SELECT name FROM work WHERE name NOT IN (SELECT template FROM NameTran)
Note that if you use INSERT INTO...SELECT the number of columns need to match. Are you really using reserved words as column names? :-) BTW in case it's not obvious from the commentary, the VFP LIKE() function is not the same thing as the LIKE operator in SQL. If you wanted to use that query with other back ends you would want to use the LIKE SQL operator. Of course, INTO ARRAY is also pure VFP... -- rk -----Original Message----- From: ProfoxTech [mailto:[email protected]] On Behalf Of Joe Yoder Sent: Friday, January 31, 2014 10:15 AM To: [email protected] Subject: Searching for a template that matches an item I'm working on a routine that gets names from a table of templates. I start with a string that needs to be converted to a name and scan the Template table to see if an existing template matches the string being evaluated. If none is found, the string is added as a template for manual editing. Here is the working code I am using. * NameTran is a table with 2 fields, Template C(40) and Name C(40) * Work is a cursor with untranslated strings in the Name field * Add records to the name translation table that are not already translated there SELECT Work SCAN select Name from NameTran; where LIKE(ALLTRIM(NameTran.Template), Work.Name); INTO ARRAY junk IF _tally = 0 INSERT INTO NameTran (Target) VALUES (Work.Name) endif ENDSCAN This is the first time I can remember ever using the "LIKE" function. I thought I should be able to use it with "SEEK" or as "INSERT INTO FOR" but I didn't find one. When I decided to try the SQL select approach I thought there should be a one liner that returns true or false but no joy there either. So my question for the experts is - Is there a better/more elegant way? Thanks in advance, Joe _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD04422A347DA34@ACKBWDDQH1.artfact.local ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
