Error In Table Definition :: Merging Tables
I am looking towards merging multiple table sets (one per user) to a single set of tables with an extra field in each table for identifying the user a particular record belongs to.
I'd prefer to make the abstraction layer on the database side to keep the changes in the application minimal. For that purpose, I am going to use triggers and create a set of views for each user.
As I understand, all indexes should be prepended by user_id field, so instead of:
create table username_posts
( id integer not null auto_increment
, title varchar(32)
, contents text
, primary key (id)
, key (title)
);
I would have to use:
create table global_posts
( id integer not null auto_increment
, title varchar(32)
, contents text
, user_id integer not null
, primary key (user_id, id)
, foreign key (user_id) references users(id)
, key (user_id, title)
);
But I get "Error in table definition", as I am using InnoDB and it requires auto_increment column to be first in the list. I was thinking about using trigger instead of auto_increment but am not sure if that's the best way.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Table Definition
I am developing a table that deals with membership dues. Thus far I have postulated that I neet the following fields: id, createdon, createdby, updatedon, updatedby, startdate, amount. Membership expires one year after the start date - the question is: should I include an expiration date in the table or just calculate the expiration date. If I include an expiration date in the table what code would I use to insert or update the table (eg. startdate+1yr)?
View Replies !
View Related
1075 Incorrect Table Definition HELP!!!
I use Navicat for the job, and I was creating a table wth the following info: ............ After filling up all this, i clicked to save the table when the following error came: 1075 incorrect table definition there can be only one auto column and it must be defined as key. I have two fileds with auto increment, as the views should aslo increase with each pageview. Should I set that as primary key too? Ist atht my only option? Is there a fix around that? If so, please could someone help me with it, and answer in step by step navicat instructions as i dont know a word of mysql.
View Replies !
View Related
Merging 2 Tables
Alright, i'm have this database where it has 7 tables. All of these tables have identical columns. However it contains information of different categories. The reason for that is to reduce the mess of having all in one tables. For one of my process, i need to combine all 7 tables together. Now, i have been searching high and low for a non-destructive method whereby i can stack all the tables' information into just one table. But what i see are things like JOIN that i can't use as i'm not joining tables through columns contain datas of the same domains. Code:
View Replies !
View Related
Merging Two Tables
I have 2 tables. table: test1 +-----+-----------+------------+------------+ | id | client_id | contact_id | strings | +-----+-----------+------------+------------+ | 1 | 55 | 3 | A | | 2 | 66 | 9 | B | | 3 | 55 | 0 | C | | 4 | 77 | 0 | D | +-----+-----------+------------+------------+ table: test2 +-----+-----------+-----------+ | id | client_id | strings | +-----+-----------+-----------+ | 3 | 55 | one | | 20 | 55 | two | | 9 | 77 | three | | 11 | 33 | four | +-----+-----------+-----------+ test1.contact_id is test2.id index I need to get all one client rows from both tables. Is it possible? I have tried to use join with these tables, but it always leaves some rows out from result. Lets take for example needed results for client 55 +-----------+-----------------+---------------+ | client_id | test1.strings | test2.strings | +-----------+-----------------+---------------+ | 55 | A | one | | 55 | C | NULL | | 55 | NULL | two | +-----------+-----------------+---------------+
View Replies !
View Related
Joining/Merging Two Tables Into One?
INSERT INTO test select testing.products_id,testing.products_percase,testing2.products_name, testing.products_weight, testing.products_model, testing.products_price, testing.products_quantity, testing2.products_description, where testing.products_id = testing2.products_id; This is what I have so far. Basically, I am trying to create one table with the values listed here from the two tables "testing" and "testing2". It says it is stopping near the where clause; does anyone know how to fix this issue so I can merge the two tables?
View Replies !
View Related
Question About Merging Big Tables?
In my applications, I need to merge three big tables(with different structures) into one big table, mySQL is: ============================= DROP TABLE IF EXISTS `my_final_table`; CREATE TABLE `my_final_table` ( ... UNIQUE KEY (`field1`,`field2`,`field3`,`source`), ); INSERT INTO `my_final_table` SELECT ***** FROM `table1`; REPLACE INTO `my_final_table` SELECT ***** FROM `table2`; REPLACE INTO `my_final_table` SELECT ***** FROM `table3`; ==============================
View Replies !
View Related
Tool For Merging Tables
I want to merge these tables into one big table and have the original tables replaced with views. Of course I can hand hack this, but I would rather have a tool do the work for me. Is there a tool that can do this and if yes where can I get it? The tool must take care of table data, foreign keys, datatypes and allow me to select columns that are the same.
View Replies !
View Related
Merging Database Tables
I need advice on how to merge database tables. I have 3 tables that I want to compress into 1 table. All tables have the same columns, but different data within. Also, all tables have an auto-incremented primary key, so I need the id's to be changed, I guess - I'm not really sure about how that would be handled. If anyone can point me in the right direction here, please do.
View Replies !
View Related
Merging The Results Of Multiple Tables
I have two tables. The first table is "users", the second table is "songs". The idea is that each user (one record in the users table) can upload several songs (several records in the songs table). I have it so that each new Song added, has a foreign key that points to a user record. So dozens of Songs could all point to the same user. My question is, I want to run a query that returns information about a user, including all their songs. So, the return table would look like: username alias songname1 songname2 songname3 username alias songname1 songname2 username alias songname1 songname2 songname3 songname4 etc.. The songnames come from the SONGS table, and username/alias come from the USERS table, but the final result should be one table. What I've managed to get working is returning all the songs of a user, but as different rows in the table: username alias songname Bob bob Happy Song Bob bob Sad Song Bob bob Super Song But I really want that to be returned as: username alias songname1 songname2 songname3 Bob bob Happy Song Sad Song Super Song So I want to merge n number of sub records, into a single user record, and return the results as 1 per user. How do I perform this feat?
View Replies !
View Related
Merging Two Tables With Diff Structures
I had installed on my phpBB3 Forum a mod to see what users viewed each topic, but then it started to not work properly with my other mods, so I found another one and installed it. But what happens is I want to put all entries that the first one had into the second one, but the thing is they have different table structures. One has one entry for every time the user viewed a topic, and the other has a view_count field, where it sums +1 every time the user visits the same topic again. The structures are: Old one: topic_id, user_id, date New one: topic_id, user_id, date, view_count Does anyone know how I could make a script in MySQL to go through the old table, and for each topic_id and user_id that are the same, make only ONE row and also count how many times forum_id and user_id are the same and throw them in the view_count field, and also for the date field, make it get always the date of the latest entry.
View Replies !
View Related
Help Needed Merging 2 Different But Similar Tables
Can someone tell me how I can import tables from another non-Joomla mysql file into Joomla? Basically it is just from one mySQL database into another. I use phpMyAdmin to import and export the entire file but I don't know how to do queries. I tried exporting the source database and then renaming all the database names to match the ones I want to merge into but all that happened was a new table was created - no merging. I really just want to select the relevant 3 fields and match them up with the fields in the database table I want to import into. I have an old program that listed all the fields of database 1 in one column and database 2 in another and all I had to do was drag the fields across to make a match and the program did the rest. Perhaps there is a similar one out there? What I am trying to do is import all the articles I have built up using ccTiddlyWiki into Joomla. The only table names of relevance from the source file are: id - title- body IN other words, just the bare essentials. The target files has a table called jos_content with columns: id - title - title_alias - introtext - fulltext in that order and others but are not important. I think it is fairly simple but I am not trained enough. I have 200 items so it would save an enormous amount of time! It seems easy but I don't know enough about queries to do it well. I'm hoping that I can import the articles and then just go through the list in Joomla adding subtitles and so on to conform to Joomla database essentials.
View Replies !
View Related
Merging Unique Emails From 2 Tables
I have Table 1 and Table2 both having unique emails within them. Table 1 being the main email Container, I want INTERSECTION of Table 1 and Table2 and the UNIQUE emails between the two should be moved to Table 1. So now that Table 1 should have All the Unique Emails within itself and also between Table 1 and Table2 ex Table 1 A B C D Table 2 F G I B C So finally Table 1 should have A B C D F G I Note: values B and C already exist in Table 1 and should be ignored. How to write this query?
View Replies !
View Related
Tutorial On Back Up & Merging Tables And Database
I just want to make sure I don't end up throwing away some valuable data as I learn mySQL. 1. What is the best way to backup data? (copy the .frm files from the folder they are stored in? a backup function? other?) 2. How can I merge databases? (Basically I have two databases that are identical in format - same tables, same columns, but have different data in them) I'll be doing this on a regular basis - as during the week I'm collecting data in two locations and they syncing it up on the weekend. And the nature of it doesn't allow me to do everything at once. 3. Any suggestions on good books/resources to learn mySQL?
View Replies !
View Related
Merging Tables With Possibly Identical Data
Im looking to merge two tables in my db (or more specifically, insert one's values into the other). This post is pretty helpful for how to set up the merge, but my problem is my two tables will possibly contain identical data - and i dont want duplicate records Let me explain in more detail - table A contains spots which may be interesting for travellers in a given city. table B has the same data, but from a different source. Both will have the same columns (id, name, subtitle, cat_id, loc_id, address, added, by), but its the data IN the tables that might be identical. As i said before, i dont want duplicate records, so if the name of the listing is the same as one thats already already in the original table, how can i make it so it isnt inserted? If it helps, i already have a script which uses fuzzy string matching (written in php) using levenstein and metaphone methods. i dont know if its practical at all, but is there maybe a way we can use that to check each item in table B against the records already in table A before its inserted?
View Replies !
View Related
Merging Rows Within The Same Table
Got this trouble that worries me a lot: Say I have tables: "users" and "userDetails". table "users": Code: ID --- userName 1 xxx 2 yyy table "userDetails": Code: ID --- dept --- city --- date 1 1 NULL NULL 1 NULL 2 NULL 1 NULL NULL 3 Suppose the entries for the second table are entered at different time spans, or by different people. I want to join this tables together and (ON ID), so that it fills the null values in the second table somehow merging them or by some special technique which is hidden to me Running extra code (either PHP or MySQL) to sqeeze (alter) the second table and select afterwards is not acceptable. I'd like a single SELECT query which would do it, while keeping the original table entries. Desired selection result: Code: ID --- userName --- dept --- city --- date 1 xxx 1 2 3 It would also be nice to learn whether I can select among the non-null values in case they match according to some criteria (like ORDER BY)... In this case there would be another non-null field in the second table (e.g. entryID), and would sort in a descending order according to it.
View Replies !
View Related
Merging Two Fields In A Table!
I have used a join to bring two tables together, which works fine, but what I now have is 2 fields with dates in which I would like to order by, and at no point are these two dates overlapping, is there a way I can merge the two together, or order by both so as they overlap. My current SQL is: SELECT * FROM tblblog LEFT JOIN tblfilmreview ON tblblog.blog_date = tblfilmreview.date_watched UNION SELECT * FROM tblblog RIGHT JOIN tblfilmreview ON tblblog.blog_date = tblfilmreview.date_watched ORDER BY blog_date DESC, date_watched DESC Thankyou for any advice you may be able to give Dan Duke
View Replies !
View Related
Merging Commands And Trying To Do A Selective Table Merge
Ok, the first problem is this. I have a large section of SQL commands to do, and I wish to know if there's a more efficient solution to do this. Background: I am working with multiple databases, each one is the same general format, but the data is dissimilar. It's incorrect in varying ways. The code below is intended to iron out some of those issues by pulling in fresh data from a second DB that is more accurate regarding aspect of the database as a whole. [MYSQL]DELETE FROM vendors WHERE entry=30239; INSERT INTO vendors SELECT * FROM ncdb_world.vendors WHERE entry=30239;[/MYSQL] The second issue is more complicated. I need a script that goes through the given table (in this case creature_proto) and selectively pulls in fresh data from another table when mindamage and maxdamage are both 1 or are both 0. The workaround I'm doing right now is manually looking at each entry in a table 36k entries long, and repeating the process every time there's a mass update. The code I'm using when I find a bad line is: [MYSQL]UPDATE creature_proto SET mindamage=11, maxdamage=14.19 WHERE entry=1721;[/MYSQL] Any assistance on these matters would be greatly appreciated. Thank you. - Aaron, Head Developer of MagicWoW Edit: Forgot to mention, I'm using MySQL 5.0.51b (comes standard with the version wampserver I'm using for MagicWoW)
View Replies !
View Related
Grant Lock Tables On Single Table Gives ERROR 1144
Tested on MySQL 4.1.20 and 5.0.33 with similar results. I don't understand why I cannot grant LOCK TABLES on a single table, while granting LOCK TABLES on all tables work fine. mysqlSELECT VERSION()G *************************** 1. row *************************** VERSION(): 4.1.20 1 row in set (0.00 sec) mysqlSELECT CURRENT_USER()G *************************** 1. row *************************** CURRENT_USER(): root@localhost 1 row in set (0.02 sec) mysqlCREATE DATABASE grant_test; Query OK, 1 row affected (0.04 sec) mysqlUSE grant_test; Database changed mysqlCREATE TABLE t (i INTEGER PRIMARY KEY NOT NULL); Query OK, 0 rows affected (0.02 sec) mysqlGRANT SELECT ON grant_test.t TO grant_test_user IDENTIFIED BY 'foobar'; Query OK, 0 rows affected (0.12 sec) mysqlGRANT LOCK TABLES ON grant_test.t TO grant_test_user IDENTIFIED BY 'foobar'; ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used mysqlGRANT LOCK TABLES ON grant_test.* TO grant_test_user IDENTIFIED BY 'foobar'; Query OK, 0 rows affected (0.03 sec)
View Replies !
View Related
Wrong Definition
Is this a wrong definition of a mysql db? (no defaults defined) : `date1` int(10) default NULL, `date2` int(10) default NULL, `num_jokes` int(8) unsigned NOT NULL, `title` varchar(255) NOT NULL, `path` text NOT NULL, etc... etc...
View Replies !
View Related
1075 Incorrect Definition HELP!!
I am a complete newbie in MySql, and jus started creating my first tabke in MySql when an error happened. I use Navicat for the job, and I was creating a table wth the following info: SongID smallint auto icrement (primary key) SongName varchar ArtistID smallint Views bigint auto icrement DateAdded date After filling up all this, i clicked to save the table when the following error came: 1075 incorrect table definition there can be only one auto column and it must be defined as key. I have two fileds with auto increment, as the views should aslo increase with each pageview. Should I set that as primary key too? Ist atht my only option? Is there a fix around that? If so, please could someone help me with it, and answer in step by step navicat instructions as i dont know a word of mysql.
View Replies !
View Related
Help On DB Definition How To Make My Indexes?
I an a complete rookie in DB. I have to build a catalog DB but I am lost in the indexes part. If you look at the attached image I have this composite Primary key made of conceptoID, divisionID, descripcionID, marcaID, lineaID and seqID also i have an index of those same fields. This is because I have to search somewhere else in the process for data having those fileds. My first question is how do I have to use the primary key in a SELECT or so statement?My second is Do I really need all those indexes or just the primary key is enogh to search for example WHERE divisionID="whatever"Also I noticed the cardinality of those indexes is zero. Do I have to build each one of them?
View Replies !
View Related
Column Definition That References Other Data In Same Row
I realize that this probably isn't a best practice, but I'm working with legacy code that has a query stored in one column of a table. Because the queries vary, the JSP page that selects a query and runs it cannot add any additional information (like a WHERE clause). I need to add a few more records to the the table, and would like the query to include a value from another field in the current row in a WHERE clause -- something like 'WHERE id = this.idlist' to use a Java-like syntax, where 'this' would mean a value retrieved from the current row of this table. Code:
View Replies !
View Related
How To Pass A Java.util.Date Object To A DATETIME Column Definition?
I am writing to a MySQL table via JDBC. I have some column definitions with type DATETIME. Here is my table definition: mysql> describe sessions; +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | uid | int(11) | | PRI | 0 | | | site_id | varchar(32) | | | | | | session_id | int(11) | | | 0 | | | context | varchar(32) | YES | | NULL | | | create_time | datetime | YES | | NULL | | | expiration_time | datetime | YES | | NULL | | +-----------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) My statement text is: Code:
View Replies !
View Related
The Merging
i want to know about of the data bases merging , that i have 2 SQL databases from my forums i want to merg the old database with the new one .... without any lose of the users or Topices or ,,,,etc. is there any way to do so, ? is there any Script can do it quikly ?
View Replies !
View Related
Merging Two Querys
I have two query that I I would like to be one, but I can't figure out how to do it. (mysql 3.23) $sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE login='%s' group by login",$usuario); $sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s' AND month='%s' group by login",$usuario,$getdate);
View Replies !
View Related
Merging Databases
Suppose I have a relational database, called "BranchA." I have a second relational database, of identical structure to BranchA, but with different data, and this one is called BranchB. Can I merge the two, into, say, a BranchC somehow on occassion?
View Replies !
View Related
Sorting And Merging
I have a table in the following style : Artnr Detail ----- ------ 110391 grey and 100391 blue color 100392 Green 100393 black 100393 monitor .... So sometimes one article has more details. My problem is, i have to get rid of that and sort the table in this way : Artnr Detail ----- ------ 110391 grey and blue color 100392 Green 100393 black monitor Is there a way to manage this with mysql or does it need php scripting.
View Replies !
View Related
Merging INSERT
in my table I have an `id` column, which is set as the primary key as well as auto_increment. In my application I currently insert a new row into the database and then do a select query to find out what the `id` column of the newly inserted row is. Would it be possible to merge these to queries into one, so I could insert the row and then query the `id` column all in one statement
View Replies !
View Related
Merging Two Db Fields
I have two different databases, and I need to merge two fields into one fields. I have fld_user_name in db1 and fld_userlogin. Is there anyway that I can do this with out having to reenter each user.
View Replies !
View Related
Merging Two Databases
I am working with 2 mysql databases for FamilyConnection CMS for my little family website. First database has 15 tables that's from an older version of fcms and the second database for a recent fcms version, has the 15 original plus 5 more new tables. How do I add the data in my old database into the new database?
View Replies !
View Related
Merging Data
I have two databases with identical structure, and I am wanting to merge the data from one table in one database with the data from the same table in the second database. I have the following code, and it seems to run without any errors but the data is not merged. Can anyone help me? (SELECT name, uname, email, url, user_avatar, user_regdate, user_icq, user_from, user_sig, user_viewemail, actkey, user_aim, user_yim, user_msnm, pass, posts, attachsig, rank, level, theme, timezone_offset, last_login, umode, uorder, notify_method, notify_mode, user_occ, bio, user_intrest, user_mailok FROM first_database.xoops_users) (SELECT name, uname, email, url, user_avatar, user_regdate, user_icq, user_from, user_sig, user_viewemail, actkey, user_aim, user_yim, user_msnm, pass, posts, attachsig, rank, level, theme, timezone_offset, last_login, umode, uorder, notify_method, notify_mode, user_occ, bio, user_intrest, user_mailok FROM second_database.xoops_users)
View Replies !
View Related
Merging Rows?
Is it possible to merge rows in SQL? eg. I have rows that contain a product ID, sessionID, Colour, Size and quantity. Is it possible in SQL to merge the rows so that if a row contains the same product ID, session ID, colour and size then the rows would be merge adding the quantity fields? I'm using this to display the merged rows but is there a way to modify this to permanently merge the rows... the reason I want to do this is I want to be able to modify the quantities eg. the form shows 3 as the quantity but that might be made up of 2 rows of a 2 and a 1 so I would only want to update one of the rows with that productID not both. Code:
View Replies !
View Related
Merging Row Field To Another - Is It Possible
If possible I need to merge a row's field's data to an another row's field's data if possible. Here is an example of the rows : I need to, if possible when I do my .csv dump to append the order_item_id 3178's order_item_item_id to 3177's order_item_item_id . Is this possible using an advanced query?
View Replies !
View Related
Merging Like Fields
I have 2 tables that I would like to Join. Each table has a Date/Time stamp and logged mysqleratures from a specific room. I would like to Join these tables, but only have one Date/Time Table. This will be a full Join, sometimes one table will have a date/time, sometimes both will.
View Replies !
View Related
Merging Data From A Many-to-many Relationship
I have three tables in my database: books {book_id*, title} authors {author_id*, name} book_author {book_id*, author_id*} The book_author table joins books and authors in a many-to-many realtionship. I join the tables with the following MySQL statement: SELECT book.title, author.name FROM book, book_author, author WHERE book.book_id = book_author.book_id AND book_author.author_id = author.author_id; The problem I have is that if a book has more than one author, the title appears multiple times for each instance of author as a seperate row in the table. eg. PHP & MySQL Web Development Luke Welling PHP & MySQL Web Development Laura Thompson Is there anyway to have the single title with multiple authors? When I GROUP BY book.title only one author is shown. eg. PHP & MySQL Web Development Luke Welling What I would prefer is something like this: PHP & MySQL Web Development Luke Welling, Laura Thompson
View Replies !
View Related
Merging Multiple SELECT Queries
I have two tables, one storing the info of the users ( one user in each record ) and the other the ids of several users in a single record i.e. (4,2,9,6), (4,2,8,3) etc. For convenience sake, i'll call it table1 and table2. What is need is to retrieve the records from table two and from the user ids, get all the users first name from table1. My problem is that i want to keep it within one query. I have tried looking up union and joins but they don't seem to meet my needs. What i would like to achieve is maybe (user1_first_name, user1, user2_first_name, user2 etc.) Is this possible at all?
View Replies !
View Related
Forum Database Merging Question
I am very new to databases although I have backed up, and imported several forum db's using BigDump. That's as far as my knowledge goes i'm afraid. I have two similar smf forum dumps which I would like to merge into one database. If you imagine and old forum added to a new forum, or two different forums that decide to merge all their boards, posts and members into one. I have spent some time now reading up on sql, searching for scripts and creating databases in an attempt to get this to work. As I have said, I am a real novice at this kind of thing and would really appreciate some help. Basically a script would be a dream come true, but I have yet to find one. I am prepaired to read, learn and experiment if somebody could just point me in the right direction. Lastly, if this has been achieved before, a link to a guide or howto would be amazing.
View Replies !
View Related
Merging Data On Remote MySql Server
I've been tasked with installing the table structure from the master database onto a remote MySQL server. The remote server will accept data and periodically, merge/dump the data to the master server. I need help with the merge/dump issue. This doesn't sound like a replication (where two database servers are made to look identical) issue but 'merge' doesn't soud right either.
View Replies !
View Related
Error #1103 - Incorrect Table Name Error?
I have always been able to copy my live database to my test database using "copy database" then my hosting company moved my site to a another server and now I can not copy my database anymore. My site works 100% with no problems but I cannot copy the database anymore This is what I am doing: I select "Copy database to" + "Structure and data" + "Add DROP TABLE" (like I have always done) but now it says a error "MySQL said: #1103 - Incorrect table name 'ABOUT' I use MySQL version 4.1.21-standard & PHP version 4.4.4 I looked this error up and it said Error: 1103 SQLSTATE: 42000 (ER_WRONG_TABLE_NAME) Actual error: Error SQL query: DROP TABLE IF EXISTS `thebill_testspecmakers.com`.`ABOUT` MySQL said: #1103 - Incorrect table name 'ABOUT' I read that I have to upgrade to ver 5.1 Oracle, SQL server etc. conform to standard SQL in respect to table naming. MySQL only does from version 5.1.
View Replies !
View Related
Dumpfile From Mysqldump Is Merging Rows For Multiple Inserts.
I am able to use mysqldump to view all the individual insertions of a table on an individual row. I have seen this occur before. Below is the desired format: INSERT INTO `database` VALUES (1,...,...); INSERT INTO `database` VALUES (2,...,...); INSERT INTO `database` VALUES (3,...,...); My dump producing the following instead: INSERT INTO `database` VALUES (1,...,...),(2,...,...),(3,...,...); Is there a way that I can configure the dump?
View Replies !
View Related
|
TRACKER
