| lakshmipathy 2005-09-23, 3:23 am |
| 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)
|