32443

How to populate a table's foreign keys from other tables

Question:

I've got the following tables, of which translation is empty and I'm trying to fill:

translation { id translated language_id template_id } language { id langname langcode } template { id tplname source domain total }

The source data to fill translation is a temporary table that I've populated from an external CSV file:

tmp_table { id translated langname tplname source domain }

What I'd like to do is to fill translation with the values from tmp_table. The translated field can be copied directly, but I'm not quite sure how to fetch the right language_id (tmp_table.langname could be used to determine language.id) and template_id (tmp_table.tplname, tmp_table.source, tmp_table.domain together can be used to determine template.id).

It might be a trivial question, but I'm quite new to SQL and not sure what the best query should be to populate the translation table. Any ideas?

Answer1:

This can be simplified to:

INSERT INTO translation (id, translated, language_id, template_id) SELECT tmp.id, tmp.translated, l.id, t.id FROM tmp_table tmp JOIN language l USING (langname) JOIN template t USING (tplname, source, domain) ORDER BY tmp.id

I added an ORDER BY clause that you don't strictly need, but certain queries may profit if you insert your data clustered that (or some other) way.

If you want to avoid losing rows where you can't find a matching row in language or template, make it <strong>LEFT JOIN</strong> instead of JOIN for both tables (provided that language_id and template_id can be NULL.

In addition to what I already listed under the <a href="https://stackoverflow.com/a/9423888/939860" rel="nofollow">prequel question</a>: If the INSERT is huge and constitutes a large proportion of the target table, it is probably faster to <strong>DROP all indexes</strong> on the target table and recreate them afterwards. Creating indexes from scratch is <em>a lot</em> faster then updating them incrementally for every row.

Unique indexes additionally serve as constraints, so you'll have to consider whether to enforce the rules later or leave them in place.

Answer2:

insert into translation (id, translated, language_id, template_id) select tmp.id, tmp.translated, l.id, t.id from tmp_table tmp, language l, template t where l.langname = tmp.langname and t.tplname = tmp.tplname and t.source = tmp.source and t.domain = tmp.domain;

Answer3:

I'm not as familiar with PostgreSQL as other RDBMS but it should be something like:

INSERT INTO translation SELECT s.id, s.translated, l.id, t.id FROM tmp_table s INNER JOIN language l ON (l.langname = s.langname) INNER JOIN template t ON (t.tplname = s.tplname)

Looks like someone just posted basically the same answer with slightly different syntax, but keep in mind: If there is no matching langname or tplname in the joined tables the rows from tmp_table will not get inserted at all and this will not make sure you don't create duplicates of translation.id (so make sure you don't run it more than once).

Recommend

  • How to parse Java properties which contains variables?
  • mysql select inside limit
  • Creating UDF with VSTO in Excel
  • How to set an entity field that does not exist on the table but does exists in the raw SQL as an ali
  • Why is django manage.py syncdb failing to create new columns on my development server?
  • how to query for min or max inet/cidr with postgres
  • How to create a new Bundle object?
  • ImportError: cannot import name Pubnub
  • sweetalert2 inputoptions from file in select example
  • Julia: How to give multiple workers access to functions that are 'include(…)' into a modul
  • JPA flush vs commit
  • pyodbc doesn't report sql server error
  • What and where is mdimport
  • Does it make sense to call System.gc() and Thread.sleep() when working on Bitmaps?
  • Transactional Create with Validation in ServiceStack Redis Client
  • ActiveRecord query for a count of new users by day
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Ajax Loaded meta Tags
  • Xamarin Forms - UWP Fonts
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Retrieving value from sql ExecuteScalar()
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • Get object from AWS S3 as a stream
  • Running a C# exe file
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • htaccess rewriting URLs with multiple forward slashes
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • Display Images one by one with next and previous functionality
  • Warning: Can't call setState (or forceUpdate) on an unmounted component
  • Arrays break string types in Julia
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • SQL merge duplicate rows and join values that are different
  • KeystoneJS: Relationships in Admin UI not updating
  • Free memory of cv::Mat loaded using FileStorage API
  • Hits per day in Google Big Query
  • How to get Windows thread pool to call class member function?
  • Checking variable from a different class in C#