Easy question on UPDATE

[Jeffrey Sheldon via SQLMonster.com]

I have one field organization_op erating_name that is on two tables vendor and
vendor_loc

I want to update the vendor name to the vendor_loc name

I tried this but get errors...


update vendor_loc
set organization_op erating_name = vendor.organiza tion_operating_ name
where organization_op erating_name like 'DO NOT%'

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

vendor is a valid table name...so I must be missing something.

jeff


--
Message posted via http://www.sqlmonster.com

[Simon Hayes]

Re: Easy question on UPDATE

See Example C under UPDATE in Books Online, and also "Changing Data
Using the FROM Clause".

Simon

[Jeffrey Sheldon via SQLMonster.com]

Re: Easy question on UPDATE


thanks Simon,

the lightbulb went off....

Simon Hayes wrote:[color=blue]
>See Example C under UPDATE in Books Online, and also "Changing Data
>Using the FROM Clause".
>
>Simon[/color]


--
Message posted via http://www.sqlmonster.com

[--CELKO--]

Re: Easy question on UPDATE

UPDATE Vendor_Loc
SET organization_op erating_name
= (SELECT organization_op erating_name
FROM Vendors
WHERE organization_op erating_name LIKE 'DO NOT%');

You would never use the proprietary UPDATE .. FROM syntax because the
results are unpredictable. It will fail to discover cardinality
violations, does not port, and depends on the physical arrangment of
the data. .