UPDATE query with JOIN and where

[PRMiller]

Having a problem getting an update query to work. What's really odd is that I can do this in Access, but not in Enterprise Manager. The query analyzer parses it correctly, but I receive the error "Invalid column name 'split'" when attempting to run with SQL.

Here's the query:

update prod
set
 prod.strSkill_CMS_Descr = stage.str_item_name
from tbl_Dictionary_Skills prod inner join stage_cc_cms_synonyms stage
on
  prod.numSkill_Number = stage.num_value
  and prod.numSite_fk = stage.numSite_fk
where stage.str_item_type = "split"

I am attempting to update prod from stage. I need the WHERE statement because the staging table (which probably isn't a true staging table) contains items including "vdn", "vector", and "split." Some splits and vectors share the same stage.num_value, so I want to make sure I'm only looking for splits in the staging table.

Thanks,
Paul

 

[jsteph]

Don't use double-quotes in EM, that may be the problem.

Make sure the tablenames are identical--sometimes in Access the tablenames on links are changed, they aren't necessarily the same as what's on the server.
--Jim

 

[Juice05]

Use single quotes not double quotes to wrap you text criteria

 

[SQLBill]

jsteph is correct....MS SQL Server uses single quotes to identify a string.

-SQLBill

Posting advice: FAQ481-4875

 

[PRMiller]

Hi Jim,

Thanks for your quick reply. Table names were correct. I changed to single quotes, then received the usual "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator." error (woops). So, for the reference of others, here's how I modified the query:

update prod
set
 prod.strSkill_CMS_Descr = stage.str_item_name
from tbl_Dictionary_Skills prod inner join stage_cc_cms_synonyms stage
on
  prod.numSkill_Number = stage.num_value
  and prod.numSite_fk = stage.numSite_fk
where stage.str_item_type like 'split'

It works beautifully.

Thanks again!
Paul

 

[PRMiller]

Wow, thanks everyone! The quick responses from all of you are another reason I love this site!