0

how to insert data into db table with 3 distinct values but also insert additional columns that are not distinct? So I have table 

data               user
__________________________________
user_id            user_id
train_code         train_code
version            version
score              score
                   first_name
                   last_name
                   nick_name

So my data composite key includes (user_id, train_code, version)

How do I insert data into data table from user table where (user_id, train_code, version) are distinct but also add score column?

Should I remove composite key constraint?

user_id |     train_code    | version

1004679 |     SGOP-0028     |   10    
1004679 |     SGOP-0028     |   11.1
Angelina
  • 2,175
  • 10
  • 42
  • 82
  • Are there multiple values for 'score' for the same (user_id, train_code, version) in the user table? If so, do you want to return all of them? – Hart CO May 28 '13 at 20:42
  • yes there are multiple values and I want to return any score from USER table. All I am concerned is if those first 3 are distinct. – Angelina May 28 '13 at 20:45
  • if you will have multiple rows from User Table having same User_Id,Train_Code,Version then you need to remove composite constraint. – sumeet kumar May 28 '13 at 20:45
  • cause I know I can make them all distinct by placing distinct in front of the: DISTINCT user_id, TRAIN_CODE, VERSION, score; Right? – Angelina May 28 '13 at 20:46
  • same user with the same training code could have taken a different course version – Angelina May 28 '13 at 20:52
  • Do you have any situation where table will contain rows where user_id,train_code,version will be same ? – sumeet kumar May 28 '13 at 20:56
  • Right, DISTINCT with those 4 fields will return the distinct combinations, but a unique key constraint won't allow for the multiple score values you need. – Hart CO May 28 '13 at 20:59
  • Do you have any situation where table will contain rows where user_id,train_code,version will be same ? NO I don't. @sumeetkumar I don't think so... :\ – Angelina May 28 '13 at 21:03
  • Then how do I add those values that do satisfy the condition, since it is throwing an error for those that do not @Goat_CO – Angelina May 28 '13 at 21:04

1 Answers1

2

If you don't mind any score when there are multiple scores for the same composite key, you could do something like this:

insert into data (user_id, train_code, version, score)
select user_id , train_code,version, max(score)
from user
group by user_id, train_code, version

Of course, my use of "max(...)" is arbitrary, and assumes that the particular score does not matter to you. i.e. you could use min(...) and it would be all the same to you.

Darius X.
  • 2,886
  • 4
  • 24
  • 51