Microsoft SQL Server forum - updating multiple fields

Author

updating multiple fields

calan

2005-09-22, 8:24 pm

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

2005-09-22, 8:24 pm

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

http://sqlservercode.blogspot.com/

Hugo Kornelis

2005-09-22, 8:24 pm

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)

--CELKO--

2005-09-23, 9:23 am

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.

Sponsored Links