• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Creating multiple records in a self-referencing table in MySQL

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi!
I have a question about how to prepopulate a self-referencing table whose definition looks like the following:



In my Java Spring Boot application I'm gonna create data.sql script file that contains multiple INSERT statements like these:


However this chunk of code raises a question. How do I know the value of the placeholder <parent_category_id goes here> given that it must be the same
as category_id, which in turn is created by MySQL automatically at the moment of record creation.
It seems that AUTO_INCREMENT is of little use here. So how would I do the task?
What I need is some sort of counter:


UPD
So far I have found this solution:


But then again these two statements don't work causing an error


The error is  

You can't specify target table 'categories' for update in FROM clause



The workaround provided below

looks ugly.
 
Bartender
Posts: 2436
13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can this article help:
https://pencilprogrammer.com/self-referencing-foreign-key-in-mysql/   ?

Basically, it adds the data and leave the self-referencing foreign key null.
Then, update the self-referencing foreign key.
 
Marshal
Posts: 28271
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The article doesn't mention wrapping the two statements in a transaction, but I think that would be a good idea, no?
 
Montana has cold dark nights. Perfect for the heat from incandescent light. Tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic