2

Like this way:

delete from `table` where id = 3;

insert into table (id, value) values (3, "aaa"), (3, "bbb"), (3, "ccc");

The count of value is hundreds, and a lot of value is the same compared with the last time, only a little records to add or delete.

I use this table to store person's property, and that property is repeated, so I insert many records in the table for one person. When update one's property, some records add or delete, and most records not changed, but when I got the new property set, I don't known which to add and which to delete. So I have to delete all the old records, and then insert the new ones, but it too slow for me, is there a faster way?

zhenze
  • 41
  • 3

3 Answers3

1

I think what you did is probably the fastest method when the number of records per person are small relative to the number of records in the whole table, the only obvious way to improve speed is by creating a non-unique index on the id column.

Another way to do what you want, if you are willing to denormalize a little bit, is to combine the properties into a comma separated values. So instead of deleting then inserting multiple rows, you only have to update a single row:

update table set id=3, values="aaa,bbb,ccc" where id=3;

With this, you lose the ability to search by values, unless you manually maintain a reverse index, and your values cannot contain a comma (or whatever terminating character you use). Another trick that might be useful when using this technique is to surround the values with terminating characters:

update table set id=3, values=",aaa,bbb,ccc," where id=3;

This way, you can still do a full-text search on values by surrounding the search term with the terminating character: select * from table where ",aaa," in values

Additionally, you lose the ability to specify unique constraint, therefore you'll have to ensure you don't have duplicate entry for values in your application logic.

Lie Ryan
  • 62,238
  • 13
  • 100
  • 144
0

are you intending to update multiple tables using a primary key? then you may have a look at this and this

Community
  • 1
  • 1
Roger Mendes
  • 57
  • 1
  • 10
  • Just one table, but update multiple records using same primary key. – zhenze Jan 09 '13 at 02:55
  • ok then the very simple way to do it is you have to use the same query with different data multiple times. or see this http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query – Roger Mendes Jan 09 '13 at 03:03
  • Thanks, but I just want to record repeated properties in one table, any way can let the sql run faster, when update properties for one person, I must delete all the record and then insert the new records, it runs so slow. – zhenze Jan 09 '13 at 03:07
  • I must also delete records that not exist now, but when I got the records set, I do not known which is added and which is not exist now, so I have to delete all the old records and insert all the new records. Is there a fast way? – zhenze Jan 09 '13 at 03:12
  • Note your id column is not a primary key. A primary key, by definition, must be unique to the whole table. – Lie Ryan Jan 09 '13 at 04:13
0

Once you set up one person with the correct rows, you can copy them to another person like this, for example to copy from id 1 to 2

insert into table (id, value)
select 2, value
from table
where id = 1;
Bohemian
  • 412,405
  • 93
  • 575
  • 722