insert into - using select and like

[ynnepztem]

Please help, I have been searching all day and have nothing.
I am using SQL Server 2000 and VB6. I have a table (NAICSCodes) with 2 fields, (CODE) and (WORD). I want to Update a field(Industry) in another table(FileFromIRS) with the (CODE) from (NAICSCodes) when a 50 character field in
my input file contains the word or words in the (WORD) field of (NAICSCodes). I am very new to SQL and am feeling quite foolish. The code that I think should work is:
SQL = "UPDATE FileFromIrs SET Industry = (select code from NAICSCODES where Word like " & "%' & inputfield & '%" & "'"
I used the double and single quotes because VB says I have to .

 

[tlbroadbent]

Don't feel foolish. We all go through a learning process. Before providing any help I need more info.

Is there a relationship between the two tables - FileFromIrs and NAICSCodes? With no relationship defined and no additional criteria your query will attempt to update every record on FileFromIrs with the same code. Is that what you want?

How many records in NAICSCodes contain the words you are searching for? If there are more than one your query will fail because a sub-query in an update query cannot return more than one record. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ynnepztem]

Thanks, OK, I went in and made the CODE field in the NAICSCodes table the primary key and made a relationship on the Industry field in the FileFromIRS table. (Only because they contained the same data) but my query still doesn't work.

The NAICSCodes table only contain the "Word" one time.

 

[tlbroadbent]

Can you post the query again? I assume it changed from the last post.

Tell what you mean by "still doesn't work." That could mean it has a syntax error or it updates too many records or it doesn't select any records or myriad other things. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.