Take the 2-minute tour ×

I have two tables with the following structure :

userid, entrydatetime, record.

tableA - has primary key (userid, entrydatetime)

tableB - has no constraints set.

I am trying to do an


but I am getting an error because tableB has all the same userid and entrydatetime.

e.g. userid = '12345' and entrydatetime = '0000-00-00 00:00:00'.

the main data that I need from tableB are userid and record. entryDateTime is less critical to me for this scenario.

How can I merge my two tables yet keeping my tableA primary key constraint? Is there a way I can randomize or autoincrement the entrydatetime field on insert?

share|improve this question
how can you have two columns userid, entrydatetime as PRIMARY? –  Mr. Alien Oct 7 '12 at 10:45
Its called a Composite primary key –  zander Oct 7 '12 at 10:46
@zander didn't knew that :D –  Mr. Alien Oct 7 '12 at 10:48
add comment

1 Answer

up vote 2 down vote accepted

You can use following query

(user_id, entrydatetime, record) 
(SELECT user_id, @value := @value + INTERVAL 1 SECOND, record from tableB);

Hope it helps...

share|improve this answer
hmmm that didnt work for me because the CURRENT_TIMESTAMP will still be the same for all the records i am inserting. –  user1258600 Oct 7 '12 at 10:52
What is your datatype for entrydatetime? is it DATETIME or TIMESTAMP? –  Shubhansh Oct 7 '12 at 10:57
its DATETIME though. :X –  user1258600 Oct 7 '12 at 11:06
Check the edited solution –  Shubhansh Oct 7 '12 at 11:18
wow that worked perfect thank you so so much. i also learnt from your solution that i can use variables like that in sql. thanks again! –  user1258600 Oct 7 '12 at 17:06
add comment

Your Answer


