Posted by lakshmipathy on 10/01/15 11:27

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(Name) values ('John')
insert into Color_Transaction(Name) 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:
>
> >How can I update each record in a table, based on a value in another table
> >with a single SQL statement?
> >
> >For example, suppose I have the following two tables:
> (snip)
> >Table1
> >
> >Name Something Color
> >-----------------------------------------
> >John GHAS Blue
> >John DDSS Blue
> >John EESS Blue
> >Paul xxxx Red
> >Ringo HJKS Red
> >Ringo FFFS Red
> >Sara hjkd Purple
> >Sara TTHE Purple
> >Jimi sdkjls Green
> >
> >
> >Table2
> >
> >Name Color
> >------------------------
> >John ?
> >Paul ?
> >Ringo ?
> >Sara ?
> >Jimi ?
> >
> >
> >How can I update the color field in table 2 to correspond with the color
> >field in table1 (so I can normalize the db and delete the color field from
> >table1)?
>
> 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)

[Back to original message]