update table from linked Excel sheet

[Cardiac]

I cant seem to reference columns within a linked Excel server
using MS SQL express

Code:

UPDATE dbo.Items
SET CCC=XL_SERVER.CCC
FROM XL_SERVER...sheet1$
WHERE BBB LIKE '7%'

i.e I want to update column CCC in my Items table from an Excel table column CCC where column BBB begins with 7

I have set up XL_SERVER correctly.

Help!

[vicoluf]

Something is wrong with this query - what is the lookup field (join field) for dbo.items in XL_SERVER...sheet1$?
Anyway try:
UPDATE dbo.Items
SET CCC=XL_SERVER...sheet1$.CCC
FROM XL_SERVER...sheet1$
WHERE BBB LIKE '7%'

[Cardiac]

I get the following error message in MS SQL Server:

Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'BBB'.

[vicoluf]

Like I wrote something is wrong with the statement. Do you have BBB field both in your linked server and dbo.item table?

If so try:
UPDATE a
SET a.CCC=b.CCC
FROM dbo.item a inner join XL_SERVER...sheet1$ b
On a.BBB=b.BBB
WHERE a.BBB LIKE '7%'