Easy question on UPDATE

[Jeffrey Sheldon via SQLMonster.com]

I have one field organization_operating_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_operating_name = vendor.organization_operating_name
where organization_operating_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]

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

Simon

[Jeffrey Sheldon via SQLMonster.com]

thanks Simon,

the lightbulb went off....

Simon Hayes wrote:

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

Simon

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

[--CELKO--]

UPDATE Vendor_Loc
SET organization_operating_name
= (SELECT organization_operating_name
FROM Vendors
WHERE organization_operating_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. .