Copying Selected Data From Two Tables Into One Table
I have a list of members in my "members" table, and they can buy tickets for an event. When they buy them, their member id number and the ticket info is stored in the "transactions" table. For the final "tickets" table, which I want to print out to put on the door, I also need to have their email and name, which is in the "members" table but not the "transactions" table.
What I want to do is insert bits of the "transactions" table into the "tickets" table, and then also use the member id number from the "transactions" table to lift out the corresponding member name and email address and insert them in as well.
Here's what I tried before I realised that I probably need something quite a bit more complicated:
INSERT INTO tickets (SELECT transaction_id, ticket_member_id, ticket_code FROM transactions), (SELECT name, email FROM members WHERE member_id=ticket_member_id)
member_id is the one in the "members" table.
Basically, here's what I want the "tickets" table to look like (the columns):
transaction_id (from "transactions")
ticket_member_id (from "transactions")
ticket_code (from "transactions")
name (from "members" using ticket_member_id)
email (from "members" using ticket_member_id)
I think I need to use a while to iterate through everything but just thought I'd check in case there's an easy way.
View Complete Forum Thread with Replies
Related Forum Messages:
Mysqldump Selected Tables And Data?
Is there anyway to only dump a related data set from a mysql database. For instance, I pick a top level table and a primary key of a row in that table and then have a cascading dump that only dumps foreign key related data from other tables. Basically I get a backup of a branch. I am using a well normalized InnoDB database.
View Replies !
Copying Table Data
Im currently writing an IT database for the company i work for *small thing, more for our use* to keep users and computers data upto date. Anyway things have developed and its going to push to company wide. So currently i have a colum structure as follows Code: UserName Department MachineNum
View Replies !
Copying Data To New Table
I created a new table for my data, and I'd like to copy the data from the first table to the second. The thing is, some of the fields have been removed, and some added. Is it possible to just copy over everything from the rows that has matching fields?
View Replies !
Copying Data From One Table To Another
I have a mysql database named swg_db in that database I have 2 tables, one named swg_user which contains the following fields: username, password, email the second table is named vbb_user which contains the following fields: username, user_password, user_email The data in table 1 in password and email is hosed, table 2 is not. I am trying to figure out the right commands, in the right order, to compare usernames, if they are the same, copy the user_password data from table 2 into the password field in table 1 and to copy the data from the user_email field in table 2 into the email field on table 1.
View Replies !
Copying Data Within A Table
I have a table with about 30 columns and one index. I need to copy a subset of this table back into the same table while changing one value. Obviously this doesn't include the index. The value that I need to change is the one that I will query against to find the subset. IE: Select * from dogs where color = 'black' Now take the results of that query and copy it back into the same table with color='white'. The table has an idex with auto_increment and primary key. I don't want to do anything that would mess this up. Does this make sense? What Im trying to do is setup a demo of some software using copies of live data. I need to alter the live data a bit to protect the confidentiality of the customer(s). Rather than re-entering data for the demo Id rather copy and alter if its possible and no too huge of a PITA.
View Replies !
Copying Data Into Another Table?
Can someone please tell me how I can copy one item in one row into another table? I am wanting to do the above when a comment is being added to the second table. Thanks in advance. My tables are like this: Article (Articleid, selectDay, selectMonth, selectYear, article_item) Users (Userid, username, password, email, ip) Commentpost (Articleid, userid, comments, date_entered)
View Replies !
Automatically Copying Data From One Table To Another
im working on a project for uni and basically i want to know if its possible for a table to automatically copy data from one table into itself (they are both in the same database) i want to create a system where a user registers giving their password, i then want the username and password to be stored in a separate table where it is encrypted and when a user wants to login into the system i want their details to be checked against the details in the table where everything is encrypted.
View Replies !
Taking Colomns Of Data And Copying It To A Different Table
I've got two pieces of software running and I want to force users to only use the one log in so I though rather than trying to build a plug in I thought a cron job would no-doubt be easier. Now getting the data I want from the right table is easy enough for me but as the columns have different ID's I need to make it transpose it into the right columns... It runs but I suspect the second part of my code is failing. Code:
View Replies !
Get Data Using User Selected Date
I'm developing web site using php and mysql. In there user has to select a date, and data belongs to that date should print.My date field type is datetime. This is my code and it is not working. GDate-> user selected date PHP Code: SELECT Rainfall,Date FROM rainfall WHERE CAST(Date as DATE)='$GDate' AND SID='$GSID'
View Replies !
No Data-zero Rows Fetched,selected Or Processed
i got this error when i m using 2 cursors there are 2 loops. cursor1 is running in outer loop and cursor 2 is running in inner loop when inner loop ends cursor2 goes out of bound. how can i set back cursor2 back to first location in result set.
View Replies !
Table To Selected
For example on my dev serve this work fine: SELECT * FROM globalUser WHERE globalUser_id = 1 While this is only valid on the prod server: SELECT * FROM globaluser WHERE globalUser_id = 1 Notice the lowercase u in the globaluser in the secord query when select the table.
View Replies !
Error: 1329 No Data - Zero Rows Fetched, Selected, Or Processed
I have some processing that requires two cursors, one within the other. I am using: DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row_fetched=1; to detect when I have fetched all the records within a cursor. However within the cursors there are other UPDATE and SELECT statements. My question is: Is Error 1329 only thrown by the FETCH statement? Or can SELECT and UPDATE statements also throw 1329? I wrote some test code that I believe shows 1329 is NOT thrown by SELECT or UPDATE. But maybe someone could add some additional info.
View Replies !
Update A Table Being Selected?
Is there a relatively easy way to fix this bad decision I made long ago? I created a table thusly: CREATE TABLE `blah` ( `mod_date` date NOT NULL default ��-00-00', `mod_time` time NOT NULL default ༼:00:00', ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I added a new field to the table that looks like: `modified` datetime NOT NULL default ��-00-00 00:00:00' I thought I could update it with: UPDATE blah SET modified=(SELECT CONCAT(mod_date, ' ', mod_time) FROM contacts); But a search in this forum showed that one can't UPDATE a table that is being SELECTed from. There was mention of a temp table (and perhaps JOIN(?)), but further searching has left me wanting.
View Replies !
Copying Tables
I'm sure I'm missing something really easy, but how can I take a copy of some tables, whilst ignoring the contents, so all I am left with is the structure of those tables, ready to import into a new database?
View Replies !
Copying Tables And Associated Linking Tables
I am having much difficulty with a query, and am hoping to get some assistance. I have the three tables shown below. I need to copy all rows in Table1 for a existing Table1.FID back into the same table but with a new Table1.FID. Both the existing Table1.FID value and the new Table1.FID value are known integers. Now the tricky part! I need to copy all rows in Table2 which are associated with a copied record in Table1 as related by the cross-table Table3. I also need to copy all records in Table3 with the new Table1 and Table2 FID's so that the new records in tables Table1 and Table2 can be related. I can use PHP if necessary, but as a mater of principle and performance, Table1 ID (primary ID, auto increment) FID (foreign ID) Data1 Data2 Data3 Table2 ID (primary ID, auto increment) Data1 Data2 Tablex12 Table1_ID (Table 1's primary ID) Table2_ID (Table 2's primary ID)
View Replies !
Copying Data
I want to copy some columns in a table of a database to another table in different database. How can I do it?
View Replies !
Copying Tables From One Database To Another
How can I use a SQL statement to copy some tables (the data and the actual table structures) from one mysql database A to another mysql database B? Note: Sometimes the structure and the data in database in A may change so I will also have to run that SQL statement in the future and copy over database B the new structures and the new data.
View Replies !
Copying Tables Between Instances
copy entire databases between two pcs (1 has MySQL 6 the other MySQL 5) just fine using... mysqldump -u username_a -ppassword_a --all-databases | mysql -u username_b -ppassword_b -h host username_a - Should be the username for the server you are backing up password_a - Should be the password for the server you are backing up username_b - Should be the username for the new server password_b - Should be the password for the new server host - Should be the hostname or ip address of the new server However I just want to copy 1 table in 1 database. The help file shows: There are three general ways to invoke mysqldump: shell> mysqldump [options] db_name [tables] shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...] shell> mysqldump [options] --all-databases If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped. I've tried various versions of #1 ( themysqldump [options] db_name [tables]) but can't get it to work. I've also tried the --tables --tables Override the --databases or -B option. mysqldump regards all name arguments following the option as table names. But couldn't get it to work either.
View Replies !
Copying Data To Server?
i have some database elsewhere than the server, and i'd like to use this db on the mysql system, but can i just copy some mysql databases to the server's data folder? (do i have to "create database..."?)
View Replies !
Copying Data Folder
I've successfully copy out a database folder under mysql/data directory and put it in another server and it works fine. No need to know the previous admin password or what for the copied database.My question is. Is this normal with mysql? Or this is normal with all DMBS, which we can copy the data folder from one server and put it in another servery, bypassing all the user security, and successfully read the database with admin privilaged given by the second server?Or this is only happened with mysql, and not with other DBMS such as ms-sql and oracle.Is there anyway to prevent this from happening?
View Replies !
Importing & Copying Multiple Tables
I was unable to copy my database from my PC (MySQL version 4.X) to my MacBook Pro (5.0.19), so I exported each of my 170 tables as a SQL file, then copied them over to my Mac. Do I now have to import them into my database one at a time, using phpMyAdmin, or is there a way to suck all of them into a database at once? I want to import all of them into a temporary database, then copy or move the ones I want to keep into an active database. So I wondered if there's a way to copy or move several tables to another database at a time. It appears that I can do this with the linked tables feature. However, when I checked both tables in my practice database, then clicked "Operations," I got the following message: Quote: The additional features for working with linked tables have been deactivated. To find out why click here. ...followed by this message: Quote: PMA Database ... not OK[ Documentation ] General relation features Disabled Does anyone know what this means and how to fix it?
View Replies !
Can I Replace Corrupt MyISAM Tables By Just Copying Files From A Backup Directory?
I have a myisam table with about 1.9 million rows in it. This table has been badly indexed. An Atmysqlt to build a new index failed and the table (I suspect the myi file) has corrupted. Fortunately this occurred on a beta site. Unfortunately this is on the same server as the live site (no budget for a beta server) I can't shutdown the database server to atmysqlt a repair. Can I just copy the files back from the backup? How can I build this vitally needed index - every time a particular query is run it sends the server to its knees. It's run a lot
View Replies !
Copying A Table With Everything?
i need an efficient way to copy an entire table, with indices and all the other info contained in the current table. these tables are massive, some with over 350 million rows so i need a way to do this in a reasonable amount of time.
View Replies !
Copying Table
I'm trying to copy a table from one mysqld to another. I've tried things like : mysqldump -h remotehost --quick --single-transaction database table | mysql database but I don't get all the data. It only copies 16789 rows when the original table has 10018090 rows. I don't get any error messages or anything (that I've found).
View Replies !
Copying One Table Into An Other
I have one table Bin0 containing data and one empty table Bin1. Now I want to move all entries from Bin0 to Bin1. Both table only have one column. What is the best way to do this?
View Replies !
Copying A Table From One Schema To Another
Is it possible to copy a table from one schema to another schema by just copying the frm file to the directory for the new schema? What is the best way to create a new database with all the tables in it automatically? I was hoping to have the tables (the frm files) included in a subdirectory and when required, just create a new schema then copy all the frm files into it.
View Replies !
Copying A Table Using PhpMyAdmin
2 phpbb boards I run have been hacked. One I've restored but the other one has had some damage to the MySQL db. A table is missing. As it's a styles table I thought I could could copy a table, from a test board I set up, to the hacked board. I tried this using phpMyAdmin and I get this error: ....
View Replies !
Copying A Large Table
I need to copy a large table without locking the database. So at the moment i have Insert into newtable select * from oldtable. This locks the database for 10 seconds which although doesn't sound like a lot of time is 2 much for what i need the database for!. I have looked at these commands FOR UPDATE. LOCK IN SHARE MODE. SQL_BUFFER_RESULT But I am not quite sure if any of these actually perform what i would like to do. The other option is to update the table line by line or in chunks using limit i suppose. But im sure there must be a better way.
View Replies !
Copying One Column From One Table To Another
I have a table, let's call it Foo, with a column of type int(11), let's call it Crud. I want to overwrite Foo.Crud with Bar.Var. Var is also type int(11), and happens to contain 1's and 2's. Of course I did my googling like a good newbie and got the following: update Foo,Bar set Foo.Crud = Bar.Var; This ran without error, but when I check it by running... SELECT COUNT( * ) AS Rows, Crud FROM Foo GROUP BY Crud ORDER BY Crud; +------+------+ | Rows | Crud | +------+------+ | 8801 | 1 | +------+------+ And it's NOT because Bar.Var only contains 1's. It contains 1's and 2's! SELECT COUNT( * ) AS Rows, Var FROM Bar GROUP BY Var ORDER BY Var; +------+------+ | Rows | Var | +------+------+ | 5043 | 1 | | 3758 | 2 | +------+------+........
View Replies !
Copying DB Table Definitions
I've been working on a MySql database project, and I will soon need to move to a different development machine. What is the best way to get all my tables set up identically on the new computer? I'm hoping there is some way to get MySql to generate (for an existing table) the code that would create that table if it were not already present. Right now the best solution I've found is going into MySql Query Browser and running a DESCRIBE command for each table. I can then right-click the resultset and export it to an html file. From each table's desription, I could easily re-create the tables on another machine.
View Replies !
Copying A Column Within A Table II
I'm trying to copy a column (Open) from a table into another column (Close) from the same table but skipping the first "cell" from the source column (Open). I tried the following query: UPDATE Table SET `Open`=`Close` WHERE `ID`="2"; The query copies the whole source column (Open) into the destination column (Close) including the "cell" I'm trying to skip.
View Replies !
Copying Table To Disk During Execution
I seem to be having an issue with a query's results being paged out to disk during runtime. I notice in processlist it saying "Copying to tmp table on disk". select col1, col2, count(ip) as 'COUNT' from db.table group by 2 order by 1, 3; None of the columns are a primary/foreign or an index. This query returns 92K rows and has taken anywhere from 50 seconds to run to over 1.5 minutes. The table size on disk is around 33MB. After doing some research i found that i should be changing the tmp_table_size system variable. Originally it was set to the MySQL default, 33554432. I set the varaible to 90,000,000 and it still started paging out to the disk (this was the only query running at the time). I would think that 90MB in space would have been enough to handle the query since that would assume over 600K row size. I actually had some trouble setting the tmp_table_size for the global variable but was able to change it as a session variable. I have also tried altering the query_cache variable (which has only slowed the query down - currently it sits at a size of 50MB) and also the sort_buffer_size but neither have made a difference. What is the magic variable that i should be messing with?
View Replies !
Copying Missing Items From One Table To Another
two structurally identical tables (national_db_1.users and national_db_2.users) in two different databases. I need to find what's missing in one and add it from the other. I would like to insert records from national_db_1.users, that are not currently in national_db_2.users, into national_db_2.users. Also, the entire row(s) should be copied, with the exception of the primary key, which can be automatically generated by the insert
View Replies !
Select Data From 2 Tables (join) REGARDLESS Of One Table Not Containing A Row
SQL SELECT o.title, o.quantity, o.price, o.product, i.supplierFROM store_order_inv o, store_inventory iWHERE o.product = i.productAND o.cart_order_id = ?-195509-3867' my only problem is if a row with Product ID doesnt exist in inventory, no result is returned, EVEN if a row exists in order_inve table with that product. So my question is, is it possible to select data from 2 tables, but where the presence of a row with matching PRODUCT field in the inventory table is OPTIONAL
View Replies !
Pull Data From 2 Database Tables Into 1 Table On My Site.
I got 2 tables: a dev_tasks and a task_history table. The task table has to record information about the task I am working on AND the history table is for the task history logs. I am trying to display info from both tables into 1 table on my site. Most of the info will come from the dev_tasks table BUT i want to dedicate on column of the displayed info for the latest log made to the task_history table. The common column to both tables is the 'taskid' column. What would the query look like to pull the info from both tables, knowing that the taskid is common to both and that I want to display the last log per tasks from the task_history table?
View Replies !
Data Insertion/Update Form Different Tables Into On Table
I have 3 tables say Employees, Benefits, Employee_Benefit_Mapping. These tables are something like this: Employee: ======== Employee_Id Name Dept_Id . . Benefits: ========= Benefit_Id Benefit Name . . Employee_Benefit_Mapping: ========================= Mapping_Id Employee_Id Benefit_Id Its a many to many relation. Hence the mapping table. Now the situation and question is: ==================================== Every employee "In a certain Dept" is eligible for some benefits by default. How can add these records with one (or minimum) insert statement(s) rather than going employee by employee? Insert into Employee_Benefit_Mapping ((Select Employee_Id from Employee where Dept_Id = xx), (Select Benefits_Id from Benefits where Benefit_Id in(xx, yy))) Will this work to insert all the data into the mapping table?
View Replies !
Multiple Tables Of Data, Single Category Table
I've searched and can't find what I'm after, so apologies if this has been covered before. I'm working on a small and simple CMS for a site I'm doing, and just as I was going to start the database I realised something... (I'm using PHP and MySQL) When it's finished, there will be articles, weblogs and content/features. Previously I've done a seperate categories table for each table I have, for example articles and articles_cats. Then a field in the articles table for the category. Now I'd like to use the same categories table for everything on the site. However, I'd really like to have a link table, so each article can have multiple categories. Would I have a table to link articles and cats, then a table to link weblog posts and cats?
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
"table Is Read Only" After Copying Table Files To New Server
I'm trying to port my MySQL tables for a database called "tracerlock" from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a ".MYD", ".MYI" and ".frm" file for every table in the database. So after creating a database called "tracerlock" on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user "mysql" in the "mysql" group. Now, when I connect to MySQL on the new machine and load the tracerlock database, "show tables" shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: mysql> INSERT INTO test VALUES(3); ERROR 1036: Table 'test' is read only However, if I create a new table called test2, I can perform INSERT statements on it with no error: mysql> insert into test2 values(3); Query OK, 1 row affected (0.00 sec)
View Replies !
Database Is Not Selected
i installed mysql....i tried to create a table at sql> it says a database is not selected.... i have no clue how to choose it... don know whether a database is created or not also...
View Replies !
Selected Secord Set
I have two tables, let’s call them Table_A and Table_B. Both have columns Record_ID and User_ID. I’d like to select all the rows from Table_B where there IS NOT a matching Record_ID / User_ID in Table_A. Example: A row from Table_B has these two values: 12345 / ABCD and there is also a row in Table_A that has the values 12345 / ABCD, I don’t want that row in the selected record set. I don’t know how to do this… Is this a JOIN of some kind?
View Replies !
|
TRACKER
