I am new to this forum. But anyway I have serious question to ask about.
I have a table say Restaurant i.e.
Sql> desc restaurant
Phone number is the primary key for this table.
The data looks like this:
I have many cusine types which are identifed by id's
i.e. '1' stands for chinese
'2' stands for 'mexican'
'3' stands for 'italian' etc.
I have another table which keeps the cusinetype ids
This has the values like this;
If I delete id 2 from attributes table, I have to delete 2 from
cusine_type string of restaurant. In my previous example
both restaurants has 2 in their cusine type.
So i have to delete 2 from cusine type and the data now becomes
So the real problem here is parsing for that particular value and then removing it from it.
The only real good solution is to redefine the table(s):
1 table Restaurant (pk = phone_number)
2 table Cusine types (pk = cusine type)
3 table Restaurant_types (pk = phone number, cusine type)
and the foreign key relations Restaurant_fk, cusine_type_fk
Now, when you want to delete a cusine type
you have to remove first all items from Restaurant_types
and the date-integrity will remain
i too would suggest you to change your table structure. but if you are so adamant about the existing table structure then in order to capture the values in the first field you can use a combination of INSTR and SUBSTR functions.
Of course, the design is ugly and needs to be changed, but for the matter of removing part of the strings ther's no need to mess with INSTR and SUBSTR. There is a function REPLACE provided that does exactly what is needed here:
UPDATE restaurant SET cusine_type = REPLACE(cusine_type, ',2,', ',');
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
jmodic. but there is a problem in using REPLACE. what about the '2's present within the phone number itself. that too would get replaced,right? so you have to find out whether '2's are present after the ',' (commas). this could be done only by using INSTR and SUBSTR, isn't it?