MySQL issue - trying to cope with poor database structure

Question

I find myself trying to cope with a poor database structure, from a program written by someone else, and am trying to write my queries to cope with that. I am trying to simulate an upsert query where there are no known unique/primary keys...

DELIMITER //
IF ( ( SELECT COUNT( f_id ) FROM `wp_cformsdata` WHERE field_name='Agent First Name' AND sub_id='395' ) > 0 ) THEN
    UPDATE `wp_cformsdata` SET field_val='Shane' WHERE field_name='Agent First Name' AND sub_id='395';
ELSE
INSERT INTO `wp_cformsdata` (sub_id, field_name, field_val) VALUES ( '395', 'Agent First Name', 'Shane' );
END IF;
DELIMITER ;

Basically my table layout is as follows...

+------+--------+------------+-----------+
| f_id | sub_id | field_name | field_val |
+------+--------+------------+-----------+

This table is used to store individual pieces of data on companies.

'f_id' is the only indexed key (primary) in this table. We don't know it's value, or if any key does even exist that contains the data.

'sub_id' is a foreign key to another table, that which the entries for each company contains.

'field_name' is the name of the information on the company (eg. Company Name)

'field_val' is the actual value of the field (eg. Google Inc.)

I am trying to update data in these tables, or insert where data does not exist. Because of the nature of this table, I can not create an index on any of the fields. Please refer to the SQL above to see what I am doing - yes it is a tad bloated. If you have any more optimal solution, please let me know!

I have tried an INSERT ... ON DUPLICATE KEY UPDATE upsert query, however this does not work as we do not have a unique key or primary key to work off here.

Simon: Yes, however (as I stated), we can not use this as we either do not know it upon update, or it is not created until insertion. — Shane, Oct 08 '12 at 08:52

score 1 · Answer 1 · edited May 23 '17 at 12:04

1

You actually treat (sub_id, field_name) as a composite key. I assume f_id is an auto-incremented column? If so, you could define a unique constraint on (sub_id, field_name):

ALTER TABLE wp_cformsdata ADD UNIQUE mykey (sub_id, field_name)

and then use INSERT ... ON DUPLICATE KEY UPDATE which not only executes the UPDATE clause if there's a duplicate primary key, but also if any other unique key constraint is violated. Also see the comments on the accepted answer here.

edited May 23 '17 at 12:04

Community

1
1

answered Oct 08 '12 at 09:21

Simon

12,018
4
34
39

Simon, yes they are composite - I was not aware I could have a unique column on a composite key? This looks like it might work. – Shane Oct 09 '12 at 01:54

MySQL issue - trying to cope with poor database structure

1 Answers1