0

I have one table in my database. Field of table are describe below.

ID | NAME | QUALIFICATION 
 1 | ABC  | Phd
 2 | XYZ  | MBA
 3 | ADS  | MBA

Now my problem is related to update QUALIFICATION record. Suppose if I update record of QUALIFICATION, it should be append new value to existing value.

For example, I am going to update record of id=1. Now I update "QUALIFICATION" MCA then it should add MCA to the existing record Phd, separated with comma. Output will looks like below.

ID | NAME | QUALIFICATION 
 1 | ABC  | Phd,MCA
 2 | XYZ  | MBA
 3 | ADS  | MBA

When "QUALIFICATION" is null then the update should not be add comma before MCA.

0

4 Answers 4

1

Thats a bad database design never store the data as comma separated string, this will make things messy in future.

You should think of normalizing the table something as for the student your table should look like

- id primary key auto_incremented
- name 
- other columns related to student

Then another table as student_qualification

- id primary key auto_incremented
- id_student ( id from student table)
- qualification

So for each student you can add as many qualification as possible to this table and can easily do add/edit/delete data

You can later easily retrieve data using simple joining the table.

Sign up to request clarification or add additional context in comments.

1 Comment

To be properly normalized there should be another table QUALIFICATION, with STUDENT_QUALIFICATION as an intersection table. However, I don't think a discussion of normalization constitutes an answer to the actual question.
0

first u have to select your existing value of Qualification column that u want to update

Using

select qualification from  tb_name where id = 1

Using above query u will get your qualification column value

suppose in

$qulification

Now update that row using

update set tb_name set qualification = '".$qualification."."your new value" where id = 1

Comments

0

May you can try this

 update employee set qualification = qualification || ',MCA' where id = 1

the above will work in oracle

EDIT:

Then you can have the case statement with it

   update employee set qualification = case when qualification is null then
                                     'MCA' else qualification || ',MCA' end where id = 1

2 Comments

Thanks for your suggestion. But when "QUALIFICATION" is null then it should not be add coma before MCA.
0 rows affected. ANd your first answer is also display 0 rows affected.
0

You can test for NULL in the SET clause, and use concatenation to format the string appropriately.

update student
set qualification = concat(if (qualification is not null
                               , concat( qualification, ',')
                               , '' )
                           , 'MBA')
where id = 1;

Here is a working SQL Fiddle (which also demonstrates behaviour with a NULL qualification).


I agree with @Abhik that this is a bad design for this specific data, and normalization is the better approach for the use case you provide However, there are other use cases where doing this sort of update would be perfectly valid so the question is worthy of a proper answer..

2 Comments

APC I need to append new data with existing. Your code replace existing. If the field have null value then don't add coma before new value. If the field have any value then add new value with coma before new value. thanks for consideration.
SQL Fiddle appears to be down at the moment but please try this revised solution and see whether it works for you

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.