0

I have a simple One-to-One relationship between table TICKETINFO and TICKETINFO_REMARK.

TICKETINFO
TICKETINFOID pk, REMARK varchar(128), TICKETDATE timestamp

and

TICKETINFO_REMARK
TICKETINFOID fk, REMARK varchar(128)

and TICKETINFOID will be foreign key from TICKETINFO table and have to populate the REMARK field of TICKETINFO_REMARK along with the REMARK field of TICKETINFO for the corresponding TICKETINFOID.

For 1 TICKETINFOID there will be one REMARK.

The insertion is working fine.

Now my problem is:

My requirement is that I am not supposed to update any record in TICKETINFO_REMARK due to some performance issue. I need to delete the record first and insert it with the same TICKETINFOID and the new REMARK.

for example:

TICKETINFO table has:

**TICKETINFOID** :     1
**REMARK**   : ABC
**TICKETDATE** :2012-06-12

TICKETINFO_REMARK has:

**TICKETINFOID** :     1
**REMARK**   : ABC

Now I want to change the REMARK in TICKETINFO_REMARK to "XYZ123" So i have to delete the entry from TICKETINFO_REMARK and reinsert it which which look like this

**TICKETINFOID** :     1
**REMARK**   : XYZ123

what will be the code to do it? please help!!!!

Jake1164
  • 12,291
  • 6
  • 47
  • 64
saptarshi
  • 97
  • 1
  • 14
  • 1
    what have you tried? Why would an update have performance issues, and not a delete followed by an insert? – JB Nizet Jun 11 '12 at 19:40
  • I don't understand the update vs delete/insert restriction. You could try the REPLACE but under the hood I think this does an update if the row key exists and an insert otherwise. – Girish Rao Jun 11 '12 at 19:47
  • The overflow issue was occurred because updating a varchar field from a small length to a larger length. That is why such requirement came . Could you please give some solution about the code. – saptarshi Jun 12 '12 at 04:00
  • which database(version) are you using? There must be better way than restricting updates. – Firo Jun 12 '12 at 07:09

1 Answers1

0

First you should select a record form "TICKETINFO_REMARK" table with TICKETINFOID.If it already found you should delete this record from "TICKETINFO_REMARK" table and then insert new record with this TICKETINFOID.It is only logic for your requirement.If you want to get sample code, please tell me which technology did you use for your CRUD operation for example JPA, HIBERNATE, IBATIS OR JDBC etc.

Sai Ye Yan Naing Aye
  • 6,622
  • 12
  • 47
  • 65