updating multiple fields

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

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

http://sqlservercode.blogspot.com/

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)

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.