Ask Your Question

How to combine parts of fields to form an updated field in order to create an intelligible Primary key? [closed]

asked 2016-09-01 01:24:07 +0200

Quarto Die gravatar image

updated 2016-09-01 01:25:35 +0200

I would like to create a Primary key field which has some meaning to me. For example, say I have list of customers FIRSTNAME, LASTNAME, but I want to make a field which combines these to make a recognizable primary key for me, such as the first seven letters of the the LASTNAME (if there are 7 letters in the name) and the first four letters of the FIRSTNAME, plus a number (just in case there are tow identical names) eg. SmithJohn001, SmithJohn002, JenkinsMary1, etc. Can I create that end result with the first and last name fields or at least combine the fields as stated and add simply the # 1 at the end, which could be modified to avoid duplicates. Another example would be to combine the numbers in the date of birth field, to the first 3 letters of the lastname and the first letter of the first name. I know this must be possible.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-11 13:16:21.480946

2 Answers

Sort by » oldest newest most voted

answered 2016-09-01 22:58:28 +0200

Quarto Die gravatar image

So far I have found the following means to get around this issue. In the first case scenario: 1. I create a field joining parts of the first and lastname: UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT (LEFT ("FIRSTNAME",2),LEFT ("LASTNAME",4)) 2. I merge these with a previously created integer ID Primary key field which updated automatically: UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT ("MERGEDFIELD","ID") 3. I can then copy the database and make my mergefield primary... There may be a simpler way:

In the second case using a date, I used the following command. 1. I create a field joining parts of the first and lastname: UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT (LEFT ("FIRSTNAME",2),LEFT ("LASTNAME",4)) 2. Then I converted the DATEOFBIRTH field to the format desired having already created a TEST field. UPDATE "MYDATABASE" SET "TESTDATE"= TO_CHAR ("DATEOFBIRTH", 'YYMMdd') NB. The dd was necessary instead of DD in this case otherwise the date was rendered e.g. 9808220Sat instead of 980808, I dont understand why. 3. I merge these two fields: UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT ("MERGEDFIELD","TESTDATE")

I can only make this field primary after the fact, hoping that no two fields are identical adding an automatically updated id field would assure that: 4. UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT ("MERGEDFIELD","ID")

I have learned that the CONCAT operator does not seem to be able to bring together more than 2 fields at a time. Hope this helps anyone interested.

edit flag offensive delete link more


Please hear @Charlie and forget to have such kind of primary key. IMO you,are about to commit a serious error. The main utility of the primary key is to link tables, i.e. if PK has dependency from the fields then you need to update all tables where it is used, every time you need to change/insert a field involved in the PK.

m.a.riosv gravatar imagem.a.riosv ( 2016-09-02 23:02:52 +0200 )edit

answered 2016-09-01 06:48:16 +0200

I think you can not do. you can not merge data into a field of a table. You may enter more primary keys if you need.

If my answer helped you, vote it with ✔ and with ∧ (here on the left)

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-09-01 01:24:07 +0200

Seen: 110 times

Last updated: Sep 01 '16