|
|
Posted by Hugo Kornelis on 09/29/96 11:27
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)
Navigation:
[Reply to this message]
|