I tried these .....

Generally when somebody asks questions in the groups it would be better
if they could give like this...

--This is a suggestion given by one database expert not me.

create table Color_Master
(
Name char(5) NOT NULL,
Something varchar(10)  NULL,
Color varchar(10) NOT NULL,
)


create table Color_Transaction
(
Name char(5) NOT NULL,
Color varchar(10) NOT NULL default '' ,
)
go


insert into Color_Master values  ('John','ghas','blue
')
insert into Color_Master values  ('John','ghas','red'
)
insert into Color_Master values  ('kumar','something'
,'orange')


insert into  Color_Transaction(Na
me) values ('John')
insert into  Color_Transaction(Na
me) values ('kumar')


update Color_Transaction
set Color_Transaction.Color = Color_Master.color
from Color_Master
where Color_Transaction.Name = Color_Master.Name

-- OUTPUT

--	Name  Color
----- ----------
--	John  red
--	kumar orange


UPDATE Color_Transaction
SET   Color_Transaction.Color = (SELECT DISTINCT Color_Master.color
FROM   Color_Master
WHERE  Color_Transaction.Name = Color_Master.Name)

--it is throwing an error
--Server: Msg 512, Level 16, State 1, Line 1
--Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
--The statement has been terminated.

--What I think is if the same names have different values it will be
very hard to distinguish between the same names
--with different colors.correct me if i am wrong



Hugo Kornelis  wrote:
> On Thu, 22 Sep 2005 19:39:37 GMT, calan wrote:
> 
> (snip) 
>
> Hi Calan,
>
> The code suggested by "SQL" will work, but it won't warn you if there
> are names with more than one associated color in Table1. Intead, it'll
> just pick one of the colors, using an unpredictable algorithm.
>
> Here's a code that will throw an error if there is more than one
> matching color:
>
> UPDATE Table2
> SET    Color = (SELECT DISTINCT Color
>                 FROM   Table1
>                 WHERE  Table1.Name = Table2.Name)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Yes. The real question is in the requirement not in the solution: What
do you want to happen if a single name has more than one colour? The
first example UPDATE has a dubious bug/feature that says "I don't care
- pick one of those colours at random". In the second case the answer
is "I do care - warn me about it so that I can fix the problem
properly".

--
David Portas
SQL Server MVP
--