Update Query

Neal · April 19th, 2007, 11:08 AM

Hi,

I have a further problem....

Using SQL 2000, Query Analyser.

There are 2 tables (MER_Product AND tomtest).
I need to append a value to dbo.MER_Product.Product_Sub_Group_ID depending on a match between a field on each table (dbo.MER_Product.Product_Reference = dbo.tomtest.Code) AND where
dbo.MER_Product.[Product Sub-Group] is equal to a specified value.

There are 95 values to set!

In both examples, if dbo.MER_Product.[Product Sub-Group] = 'En' Then
dbo.[Product Sub-Group] = 1

I've tried this...

Code:

UPDATE dbo.MER_Product
SET Product_Sub_Group_ID = '1'
WHERE dbo.MER_Product.Product_Reference =  dbo.tomtest.Code AND dbo.[Product Sub-Group] = 'En'

Error is "Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.tomtest' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo' does not match with a table name or alias name used in the query."

And this...

Code:

INSERT INTO dbo.MER_Product
(
Product_Sub_Group_ID
)
WHERE dbo.MER_Product.Product_Reference = dbo.tomtest.Code
SELECT 
CASE dbo.tomtest.[Product Sub-Group]
WHEN 'EN'  THEN '1'
WHEN 'Ar' THEN '2'
END

Error is "Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'."

Any help please?

Thanks in advance,

Neal

A Northern Soul

happygv · April 19th, 2007, 11:32 AM

Quote:

quote:Originally posted by Neal

Code:

UPDATE dbo.MER_Product

Code:

SET Product_Sub_Group_ID = '1'
WHERE dbo.MER_Product.Product_Reference =  dbo.tomtest.Code AND dbo.[Product Sub-Group] = 'En'

Error is "Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.tomtest' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo' does not match with a table name or alias name used in the query."
And this...

You are missing FROM clause there

Code:

UPDATE dbo.MER_Product
SET Product_Sub_Group_ID = '1'
FROM dbo.MER_Product, dbo.tomtest.Code
WHERE dbo.MER_Product.Product_Reference =  dbo.tomtest.Code AND dbo.[Product Sub-Group] = 'En'

Quote:

quote:Originally posted by Neal

Code:

INSERT INTO dbo.MER_Product

Code:

(
Product_Sub_Group_ID
)
WHERE dbo.MER_Product.Product_Reference = dbo.tomtest.Code
SELECT 
CASE dbo.tomtest.[Product Sub-Group]
WHEN 'EN'  THEN '1'
WHEN 'Ar' THEN '2'
END

Error is "Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'."

check the INSERT syntax on BOL. You can't specify WHERE clause in INSERT.

cheers

_________________________
- Vijay G
Strive for Perfection

Neal · April 19th, 2007, 12:03 PM

Vijay,

Yet again you come to my aid (I was close but not close enough in my first example).

Many thanks

Neal

A Northern Soul