updating multiple fields

[calan]

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:

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)?

I know I could open table2 and loop through within my app; just wondering
about a single SQL statement that would do it. I need a similar technique in
other places as part of my app.

Thanks,

Calan

[SQL]

Update t2 set color = t1.color
from Table1 t1
join Table2 t2 on t1.name =t2.name

http://sqlservercode.blogspot.com/

[Hugo Kornelis]

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)

[lakshmipathy]

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

[David Portas]

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
--

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

CREATE TABLE Foobar
(user_name CHAR(15) NOT NULL,
something VARCHAR(10) NOT NULL,
color_code INTEGER DEFAULT 0 NOT NULL
REFERENCES Colors(color_code));

Are you using Land color numbers? PanTone? Another industry standard?

CREATE TABLE Colors
(color_code INTEGER NOT NULL,
color_name VARCHAR(10) NOT NULL);
INSERT INTO Colors VALUES (0, 'Mixed');

But ignoring that, I think what you want is:

CREATE TABLE FavoriteColors
(user_name CHAR(15) NOT NULL,
color_code INTEGER DEFAULT 0 NOT NULL
REFERENCES Colors(color_code));

INSERT INTO FavoriteColors
SELECT user_name,
CASE WHEN MIN(color_code) = MAX(color_code)
THEN MIN(color_code) ELSE 0 END;
FROM Foobar
GROUP BY user_name;

If your data is dirty and someone has more than one color, this will
give them a special code. You would, of course, never use the
proprietary, unpredictable UPDATE.. FROM syntax.