[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Is there a way to use 1 table to define the colums of another table

Posted on 2011-09-30
5
Medium Priority
?
309 Views
Last Modified: 2012-05-12
iI would like to create a mysql database table that will contain 3 fields.
Name
Description
default value

I would then like the records of this table to be used to create the fields of another table.

so that if a record contained the following

name = x
description = y
default value z

and another record contains

name a
description b
default value c

then the fields of the otehr table would be x and a.

the 2nd table would update with a new field whenever a new record was added to table 1.

furthermore when a new record is created in table 2, field x would default to z if no other value was specified at record creation.

What is the best way to go abvout this?
0
Comment
Question by:Stuartjrose
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 5

Accepted Solution

by:
Plk_In_EE earned 1000 total points
ID: 36895972
Hi there,
you can update one table values using the other table update.

UPDATE dest_table1
SET
filed1 = (select field1 from src_table t2 where t2.pk = t1.pk)
field2 = (select field2 from src_table t2 where t2.pk = t1.pk)
...etc.
WHERE
EXISTS (select 1 from src_table t2 where t2.pk = t1.pk)

Hope this helps for you.

thank u
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 1000 total points
ID: 36896088
Use triggers to automatically update table2.  From your description I don't really understand the structure of table2 but the trigger should look something like:

delimiter |
create trigger trigger_name
after insert on table1
for each row
begin
  insert into table2 (x, a) values (NEW.name, NEW.description);
end; |
delimiter ;

See http://dev.mysql.com/doc/refman/5.1/en/triggers.html for more info.

For assigning of default value, maybe you can try the ifnull() function, though I'm not sure if works within a trigger/stored procedure body as the examples in (http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull) are all for selects.  If it doesn't, then put a conditional statement in the trigger body to determine what will be assigned to table2.
0
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 36900512
What are you trying to accomplish?  We may be able to come up with a better way to solve your problem.

To go with what you posted, you would probably have to use triggers to make this work.  You would need to create an insert trigger on table 1 so that any insertion into that table would execute an alter statement on the second table to add the new column.

Likewise, you would want to create an insert trigger on table 2 to look at each column such that any data inserted into table 2 would execute a trigger on an empty column that then does a select from table 1 to get what the default value should be.

The second trigger may be a little complicated since it needs to look for all fields in the table that have an empty or null value and then do a select from table 1 to get each default value.

0
 

Author Comment

by:Stuartjrose
ID: 36900607
thanks for the help people.

To clarify the Question.

the idea is that table 2 will be used to represent an rpg character with fields for attributes, skills and skill values.

I want the fields for skills and skill values to be user customizable. i.e a user can add a skill to a modern day skillset that will not be usable in a medieval set.

So that a user can add a skill a description of the skill and set it's default value and that will then become available to that user for every character they create using that skillset (table).  I've tried to abstract that as much as possible for this explanation.

this is a self set project based on my interests in order to teach myself more advanced sql than just a query for all records with a value =x.  Even if it only teaches me that what i want is best done outside SQL.

thanks for teh help everyone.
0
 
LVL 32

Expert Comment

by:awking00
ID: 36905209
Can you describe the original table (i.e. provide column names and datatypes) with some sample data, then indicate what the structure and values should be in the other table?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question