CODEWITHSUNDEEP
mysqlnavicat
user1054495
user1054495

Reputation: 15

Create Table in MYSQL / Navicat column with values from another table

Now this may be something I imagined, or something real either way I am stuck on what to Google for (other search engines are available....no really there are)

I was once working on some mysql tables somebody else had set up, I was working with navicat and I went to insert data into this table and got to a field that could only be populated with values from another table, had a drop down box and everything (though I know that was navicats doing) like it had some sort of coded link between the tables. This impressed me, so much so that I opened my mouth wide in awe..

I am now wondering how I do this, as it seems perfect for my linking tables, you know the id_from_table_1 links to id_from_table_2 tables. Especially with the drop down box navicat offered when this was set up, better still if it could work like a select box where I see the name of the item in the list but it inserts the id -> I know I may be asking for a little too much here, either way the first of my queries intrigues me. Was this some coded link in mysql or some function coded into navicat? either way I would very much like to recreate it, so I to can appear like a wizard to my colleges perhaps even gaining the title "Harry Potter" amongst those that see my awesome skills.

Upvotes: 0

Views: 7149

Answers (2)

user1054495
user1054495

Reputation: 15

I found out by clicking around that I was talking about foreign keys. Once set-up properly Navicat can provide a drop-down box with the foreign table details, making data entry that little bit easier and that little less prone to human error :) not to mention faster table requests which is nice :)

Design Table->Options(tab)->Engine->InnoDB (for both tables) then Design Table->Foreign Keys(tab)->Fill out various details

now when entering data into the table via Navicat on the field with the key it will show a drop-down icon, click that then hit settings and choose the data from the foreign table you want to display.

handy for relations table

now

| client_id | product_id |
--------------------------
|     1     |     23     |
--------------------------
|     1     |     98     |

is a lot easier to play with as entering a product_id in Navicat will show me the products table and let me select one, and same for the client table.

pure genius :)

Upvotes: 0

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

In Navicat, go to Tools -> Data Transfer. Select source and target, and hey presto that's your transfer done. If that isn't suitable then you may need to put together an UPDATE query manually to manually update fields in one table based on fields in another.

Upvotes: 0

Related Questions