mysql - How to update foreign keys while inserting rows? -


i have 2 tables following layout ...

-------------    ------------------------- | master    |    | history               | -------------    ------------------------- | id | name |    | id | name | master_id | -------------    -------------------------

the history table contains rows master_id null. copy master table.

insert master (name) select name histories h h.master_id = null

how can update master_id in history table id of associated object in master table? update should happen in same step insertion made. use mysql if of interest.

it possible insert master table records ids based on information other table.

for example, can use histories.id + last master.id:

lock tables master write, histories h write;  select id master order id desc limit 1 @last_master_id;  insert master (id, name)     select h.id + @last_master_id, h.name histories h;  update histories h set h.master_id = h.id + @last_master_id;  unlock tables;

this works long use integer ids , if don't mind new id based on other one.

if wanted use non-deterministic ids, such uuids, solution require few more steps:

  • add temporary column histories table.
  • fill new ids (update histories set tmp_uuid = uuid(), that).
  • insert master select tmp_uuid histories...
  • update histories
  • drop temporary column.

one advantage of using uuids don't need lock tables. don't need worry other session inserting record , taking id, because other session generates different id.


Comments

Post a Comment

Popular posts from this blog

matlab - Deleting rows with specific rules -

i got 20*3 cell array , need delete rows contains "137", "2" , "n:t" origin data: 't' '' '' 'np(*)' '' '' [ 137] '' '' [ 2] '' '' 'are' 'and' 'np(fcc_a1#1)' '' '' '1:t' [ 1200] [0.7052] '' [1.2051e+03] [0.7076] '' 'are' 'and' 'np(fcc_a1#3)' '' '' '2:t' [ 1200] [0.0673] '' [1.2051e+03] [0.0671] '' 'are' 'and' 'np(m23c6)' '' '' '3:t' [ 1200] [0.227...
Read more

php - MySQLi multi_query results for later use -

i using mysqli multi_query work several select statemets @ time. know how handle results, able use them later in code. example: <?php //connection stuff $query = "select name, surname database1;"; $query.= "select car, year, type database2 carid='1';"; $query.= "select product, price database3;"; if ($mysqli->multi_query($query)) { if($result = $mysqli->store_result()) { while($row = $result->fetch_row()) { --> here? } } } ?> <html> <div id='persona'> <?php foreach() { --> print name + surname } ?> </div> <div id='cars'> <?php foreach() { --> print car + year + type } ?> </div> <div id='product'> <?php foreach() { --> print product + price } ?> </div> </html> one more thing, prepared statements not possible when using multiple_query, right? there no benefit in putting unrelated queries in 1 multi ...
Read more