Posted by --CELKO-- on 10/01/25 11:27

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.

[Back to original message]