SQL*plus Inserting New Row With Values = To Existing Rows Data
I am trying to insert a new employee into an empolyee table and get some of the values (namely phone no.) to be the same as what another employee currently has. This new employee is replacing the old employee, but i have to keep the old employee alive in the db as other data is reliant on its existance.
I was wondering how do i insert the new employee asking the, VALUE ('etc', 'etc',) line, to assign a value to certain fields equal to the value contained in other employee rows.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Inserting 100 Rows Of Data Into An Existing Table
I want to import 100 rows of data from an Excel spreadsheet into an existing MySQL table. Both the spreadsheet table, and the MySQL table have the same exact format & headers. In MySQl Query Browser, I can locate only the Edit function, which seems to only allow me to type in the data line by line. Can I import the data in MySQL Query Browser, or do I need another product? And if I can import, how do I do this?
Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db. Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4. What I would like to do is take the entire row and duplicate it by the number of the quantity field. This would result in the same product 4 times instead of one product with qty of 4. Reason, I am going to serialise all the products so that they each have unique barcode from the id field. I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??
How To Import Data Into Existing Rows Of A Table?
I have moved 665 records off of Filemaker into mysql-4.0.18. I had a great deal of trouble exporting a text field, possibly because it had control characters that interferred with my field delimiter (also it was from a Mac, and I'm new at this.) So I have a table called invoices and another table called tasks, that has only an invoice number and the work billed on that invoice number. To see the work billed along with the other invoice data I have to do a join, and this works but is clumsy for me. Since tasks.work is a text field of 'infinite' variety I don't see the sense of keeping it in a separate table like I would with clients' info; it belongs *in* the invoice table, not just with it. Is it possible to import this text data into a field in the invoice table? I've messed around with this but it always appends the data as new records. Perhaps I was doing an insert back then. Is the answer something like 'load data infile 'workdone.csv' into invoices (workdone);' The invoices table was filled in a certain order (by invoice number) and the workdone.csv was exported in the same order, so each invoice row should get its correct text. Is this a good thing, or am I thinking too 'flat-file-ish'?
How To Import Data Into Existing Rows Of A Table?
I have moved 665 records off of Filemaker into mysql-4.0.18. I had a great deal of trouble exporting a text field, possibly because it had control characters that interferred with my field delimiter (also it was from a Mac, and I'm new at this.) So I have a table called invoices and another table called tasks, that has only an invoice number and the work billed on that invoice number. To see the work billed along with the other invoice data I have to do a join, and this works but is clumsy for me. Since tasks.work is a text field of 'infinite' variety I don't see the sense of keeping it in a separate table like I would with clients' info; it belongs *in* the invoice table, not just with it. Is it possible to import this text data into a field in the invoice table? I've messed around with this but it always appends the data as new records. Perhaps I was doing an insert back then. Is the answer something like 'load data infile 'workdone.csv' into invoices (workdone);' The invoices table was filled in a certain order (by invoice number) and the workdone.csv was exported in the same order, so each invoice row should get its correct text.
Table Joins On Existing/Non-Existing Data
I have a question that involves a joining tables on potentially non-existing data, but we will know the expected values whether they exist or not. Say we're keeping statistics on any given number of sports for attendances over weekly time periods. There's two tables to hold data. ------------------------------ TABLE: StatisticTypes id name ------------------------------ TABLE: Statistics id statisticTypeId value date ------------------------------ Then, say, we have three entries in the StatisticTypes table for types of sports we're keeping data on...
Check Existing Values
In my web site I'm using PHP, & MySQL. There its needed if a user is trying to be registerd using a existing user ID. That is I think, it should check such a value existing and if exists it must display "Existing User ID".
Add Multiple Rows To An Existing Table?
I have a table named manufacturers, it has one row in right now. I want to upload all of my manufacturers into the table. Is there a way to add multiple rows to the table at the same time? If not, what is a quick way to get about 1,000 manufacturers uploaded into a table?
Inserting Values
I need a big database only 2 table. i have been able to do the tables and all not problem. I can add and delete no problem. One of my problem is I do not want duplicate entries for one field. The problem is because database is so big and the entries have duplicats i have to add them in 1 batch or I will be adding then one by one forever. Is there a way around this? Second I have an entry like below INSERT INTO `table` VALUES ('2005.02.13 19:06:35', '853edbd1c9fdbcf0a9ff5cbf5e347267', 'sajdhs'dhs'); Second I cannot put the above in mysql unless as it throws and error becuase of the ' used in thrid entry. I need these entries to go in as they are.
View That Wont Return Existing Rows
i have a problem performing simple query over view. To make it short, so anyone can recognize whether it is something im missing, this is the problem: * i have created few levels of views (some of them are based on actual tables and others are based on other created views or some mixed) * lets say the name of the view i have problems with is "aView" * when i perform "select * from aView" i get to see the view is correctly populated * when i add condition to the query above in some cases it returns ok results but in others it returns 0 rows (which is wrong) select * from aView where a=1, works fine select * from aView where a=1 or b=2, works fine select * from aVew where a=1 and b=3, returns 0 rows --- which is wrong(of course not by itself but in my case there are rows with ok values) same goes with using only the condition on b column a.) why wouldnt it fetch rows that are cleary in the view?? b.) is there any limitation on how high structure can we build using views
Inserting Hexadecimal Values
I am attempting to insert data into a field of CHAR(16). The data is hexadecimal ie. x'24te48we123456788765432112345678' I have not been able due to syntax errors. Keeps telling me the "data is too long". Does not seem to recognise that it is a hex.
Add Additional Data To Existing Data In A Blob Field
I have a Blob field called "category" that we use to store different categories of job types that our users set up so they receive emails based off of the ones that they have picked. What I need to do is to add 4 new values to the beginning of each users "category" field. I am not really sure how to do this except that I might need to use UPDATE to get it done. This is where it will get tricky, I need to check for the 4 values to see if they already have them and just add the oines that they don't have. Here are the 4 values that I need to check for and add. Allied Health Care, Nursing, Rehab/Therapy, and Other/Pharmacy/Physicians. Here is what a current list of values would look like in the users table and the category column: Code:
Inserting Values Into A Mapping Table
I am using a mapping table with two primary keys (ID1, ID2). When I try to insert data into this table I get the following error: Quote: Violation of PRIMARY KEY constraint 'PK_myTable'. Cannot insert duplicate key in object 'dbo.myTable'. The statement has been terminated. I am able however to insert values if I do it directly into my table. Here is an excerpt of my comm = New SqlCommand( _ "INSERT INTO myTable (ID1, ID2) " & _ "VALUES (@ID1, @ID2)", conn) ' Add command parameters comm.Parameters.Add("@ID1", System.Data.SqlDbType.Int) comm.Parameters("@ID1").Value = ID1List.SelectedItem.Value comm.Parameters.Add("@ID2", System.Data.SqlDbType.Int) comm.Parameters("@ID2").Value = ID2List.SelectedItem.Value
Inserting Multiple Values In One Column
I have a web form that has a drop down list where you can select more than one item. I'd like both of these values to be entered into the same column in a table. For example, the table is set out as follows TAG_TABLE tag_id user_id software_id all of the above are integers whose value is referenced in another table. eg the software_id table is as follows SOFTWARE_TABLE software_id software The dropdown list in the form is populated from the software table. What happens is that when I select an item from the drop down list say Microsoft Visio it enters the corresponding integer in the main table. But if I select Microsoft Visio and Micrsoft Project, I want both of those integers to somehow be added into the user_id column in the TAG_TABLE. I also have a web based reports form .....
Update Values On Inserting Duplicate Index
I am very new to mysql. I have a question about using the "on duplicate update" clause with insert command. my table "data" has two columns, field1 and field2, where field1 is the index and is "unique". when I run insert ignore into `data` ( `field1`,`field2`) values (1,2) (2,6) (2,9) (5,1) I got 1,2 2,6 5,1 because the third value pair, (2,9) was ignored due to duplication in field1. here is what I want to do: when inserting new record with duplicate index, I want the existing record gets updated. in the above case, I want the output look like the following after the insert command: 1,2 2,9 5,1 after searching mysql online document, I only found the "on duplicate update" clause can do something similar, however, all the examples only show setting the duplicate record to somthing unrelated to the new values.
Inserting Money (Dollar Amount) Values
I'm having a problem inserting money (dollar amount) values into a MySQL 5.0 database. I'm using a text box in a ColdFusion web form that allows users to enter the dollar amount for sales. For example, they can enter 1,000.00 for a thousand dollar sale. Everything seems to work fine until the user enters any value that has a comma such as 1,000.00 or 150,000.00 or 1,000,000.00, etc... I've tried using different data types within the database for this column. I've tried Int, BigInt, and Decimal (10,2) but nothing likes the commas. Does anyone know how I can get around this problem?
Best Way To Get Data From Existing Database
what I'm trying to do is get data from an exhisting oracle database that is not looked after by myself but I have access to. What I want is for my MySql database to mirror the oracle one or update every 5mins or so. I have done a quick search but as I have only made simple databases before I'm not sure what to search for!
Edit Existing Data
I have just started to learn MySQL and this question might be silly. I have a MySQL table with user information. I want to edit this table in a website. I need to load existing data in a HTML form and then edit the fields and update them. I searched the net but could not find anything .
Convert Existing Data
i am working on a project and i need to convert already existing data into the mysql date format. I was told for the project that the date format was necessary. my first question is what is the advantage of having the mysql date formate which goes like this yyyy-mm-dd. the format my data currently is in is like this mm/dd/yyyy. The second question is how to convert from mm/dd/yyyy to yyyy-mm-dd
MySQL Inserting Rows Out Of Order
Using PHP, I generated a query file that has several thousand INSERT statements in it. The problem is that when I run the INSERT query, the rows are NOT added to the table in the order in which they occur in the SQL file. The first few hundred INSERTS are inserted toward the end of the table. What is the problem? The way around this is for me to resort the table based on one of the columns so the proper order is maintained. I'm using this with forum software, so it is critical that the table be in order else my boards are out of order. Is there a problem with MySQL or with me? Any ideas why rows would be added out of order?
Import Data Via Ssh Into An Existing Table
I want to import data from several .sql files into an existing database, into one table. I tried using the following command: mysql -e [sqlfile] [db_name] But the problem was when I imported the next file, it would overwrite the previous imported data. Could anyone let me know how to do this correctly, please?
MySQL -- Editing Existing Data In A Row
I looked through ALTER table, and some tuts over at w3schools (my heros) but I can't seem to find a way to edit EXISTING data in an EXISTING row of a table.
Append Existing Data In A Table
I am working on an existing osCommerce store. There are over 200 products, each of which needs the associated image file name changed. Rather than do this manually (any number of ways), I am unsure if there is a query I can run to save me lots of aggravation. What I would like to do is update whatever is in the field and change it to add the same 5 characters: The Table is Products The Field is products_image Examples of existing data in this field are: abcdef.jpg ght.jpg fire_777.jpg oops.gif (the replacement image is a jpg) The result I seek is: abcdef_th.jpg ght_th.jpg fire_777_th.jpg oops._th.jpg Logic tells me to start at the end of the data, remove the last 4 characters (.jpg) and append 5, in this case: _th.jpg Could someone please give me a hand with this? I have spent many days redoing the image files and they are nearly ready to go into a live store.
Append Data To Existing Cells
I have an existing table. I need a way to append data in one of the columns. How do I append data in cells so I do not overwrite the existing data already in the cells in the column? The fields in my table are `Source` , `Theswords` , `Topic` , `Subtopic` , `References` The table name is ViewNew The column I want to append is Theswords
Inserting 300M Rows From A Php Script To Mysql
I'm new to Mysql and Php and for the last week or so I'm trying to add 300 Millions rows to a table with 10 columns, the system is very slow and it stops at 200k rows after 4 hours. What I did was to generate a Php script that will automatically insert the same data to the database 300M times. Why is it so slow? Why is it that I can not get pass 200K rows? What can I do in order to improve performance? Note: Already tried to change some parameters in my.ini like........ Example: # Set buffer pool size to 50-80% of your computer's memory innodb_buffer_pool_size=70M innodb_additional_mem_pool_size=10M etc... P3 933Mhz, 640MB SDRAM, 30Gb hd
Amending A Hierarchical Structure On Existing Data
I want to amend my table such that the following... SELECT * FROM words; +-------------+------+------------+ | root/parent | pos | word | +-------------+------+------------+ | abandon | Verb | % | | @ | @ | abandon | | @ | @ | abandoned | | @ | @ | abandoning | | @ | @ | abandons | | abbey | NoC | % | | @ | @ | abbey | | @ | @ | abbeys | +-------------+------+------------+ would instead read... SELECT * FROM words; +--------------+------+------------+ | root/parent | pos | word | +--------------+------+------------+ | NULL | Verb | abandon | | abandon | Verb | abandoned | | abandon | Verb | abandoning | | abandon | Verb | abandons | | NULL | NoC | abbey | | abbey | NoC | abbeys | +--------------+------+------------+ Thus dispensing with the need to rely on the table's natural order, but I'm a bit stuck as to how to go about this. I'm happy to add a numerical key to the table if it makes this process easier. In my mind (not an especially logical place) the logic is something like this: if parent is not "@" set var_parent = parent and var_pos = pos else set parent = var_parent and pos = var_pos finally delete from words where word = "%" But how do I turn that into something useful?
Import Additional Field Data To Existing Table
I've got my data all set-up in a mysql table and all functioning well. We have decided that we need some additional bits of data for each record and we have that data in a csv file. We've created the fields in the mysql table. In the csv file, I have the userid (to match that in the current sql table) and the additional data with the column names in the csv file matching exactly the new fields in the sql table. The additional fields in the original sql table are empty, so we can just simply write-over these fields with the data from the csv file. But...we are having problems with the import using the MySql administration tool on our server. If I select a csv file upload, I get a number of fields is not same error If I use csv load data upload, it just overwrites the first few lines of current data and doesn't put the new data in the correct fields. Can anybody help, please? If csv load data is the correct format to use, what do I put in the import tool for these variables (given that I have a standard csv file that has been created using excel) Replace table data with file (yes/no default is no) Ignore duplicate rows (yes/no - default is no) Fields terminated by(default is Fields enclosed by(default is ") Fields escaped by(default is ) Lines terminated by(default is auto) Column names (default is blank) Use LOCAL keyword (yes/no - default is yes)
Duplicate Existing Database (innodb), Structure Only, Without Data
There's a database (innodb) named "test1". I want to create a new database with exact the same strcuture, but since there are many tables in this db i do not want to retype the whole commands. I'm looking for a possibility to duplicate this database, any ideas / tools?
How Do I Generate Data Definition Statements For Existing Tables?
I cannot remember how to generate a create statement for an existing table. I tried searching the documentation but I don't think I'm searching for the right text. I'd also like to know if there is a specific name for this task and what section it is under in the docs.
Migrating Data Into Existing Mysql Schema Tables
i understand how to migrate data into mysql and to use the migration tool to convert a database to a mysql schema, however, I'm not sure I understand how to migrate specific data tables/columns in the source file to specific tables/columns in the destination mysql tables (where the schema already exists and I'm simply importing data itself).
Values From Different Rows
I have Orders and Payments. Sometimes the Orders do not have Payments. So, I decided to use LEFT JOIN. Furthermore, some Orders have 1 related Payment, and some Orders have 2 or even more related Payments. Now, I want to combine the sum of all Payments and the last Payment in one row. select Orders.SeqNr, sum(B1.Amount) Total_paid, max(B2.SeqNr) Last_record, B2.Amount Last_amount from Orders left join Payments B1 on B1.FK_Orders = Orders.SeqNr left join Payments B2 on B2.FK_Orders = Orders.SeqNr group by Orders.SeqNr If there is only 1 payment per order, then I will see perfect results. However, if there is more than 1 payment per order, then the results are wrong... The column Last_amount does not contain the amount which belongs to the column Last_record.
Inserting Data
Everytime I insert data into a table, The rows are rearraged so the primary key column is in alphabetical order. How does this happen? and how do I fix it?
Inserting Data
if I want to insert data into two tables how do I do this? Is this right? I'm guessing not as it doesn't work What should I do? $query = "insert into requests (word,status) values ("$req","pending") AND insert into known_by (word,user_name) values ("$req","$user_name")";
Inserting New Data
I have a form that has name, address, city, state, zip. All this goes into a single table. I want to add an additional table to put the cities into. I want to pull the cities from the city tables into a dynamic dropdown to choose the cities from.My problem is if I have a city already in the city table, I dont want to add it again and have duplicate citys in the dropdown. Anyone have a clues on how I would do this?
Inserting Data
I'm trying to do something like this CODE$query="INSERT INTO ".$table_name." ('catagory', 'link', 'link_description', 'link_title', 'link_name',) VALUES ('', '".$link."', '', '', '') WHERE catagory=".$catagory."";
Problem Inserting Data
I'm having an issue inserting data into a MySQL database using PHP code which has worked before. Some of the fields in the form have been removed and so are not passing data across to the insert script and the insert has been altered to show this, apart from that everything is the same. Could anyone help me diagnose what is going on this code to not make it work, I've been looking for the last 2 hours and just cannot find the problem. Data entry page PHP <? if(isset($_GET[submit])){ $error='' if ($_GET['heightcm']=='') { $height=($_GET['heightinches']*2.54); } else { $height=$_GET['heightcm']; } if ($_GET['widthcm']=='') { $width=($_GET['widthinches']*2.54); } else { $width=$_GET['widthcm']; } if ($_GET['depthcm']=='') { $depth=($_GET['depthinches']*2.54); } else { $d
Trouble With Id + 1 When Inserting Data
I'm trying to find a good alternative for auto_increment, the reason is that I need to know what the next value of id will be before inserting data into the table and I haven't found a relyable way of doing that with auto_increment. My plan was to use a commend like: INSERT INTO temp (id, test) VALUES (MAX(id)+1, 'foo') But I am getting the error: ERROR 1111 (HY000): Invalid use of group function.
Inserting Data Into Table
My task is to create a table which works as a "linker" of two other tables. In this linker table as foreign keys I am using keys from this two tables. I am inserting data into first table t1 in one loop in Perl script, then in a second loop I am inserting data into table t2 and immediately after into t3 which is a linker table between t1 and t2. I have no problems with id (primary key) from t2 , but while extracting keys from t1 there is message: Subquery returns more than 1 row after subquery: insert into t3 (t1id,t2id) values ((select id from t1 where name like 'x'),(select max(t2id) from t2)); simply because value '' are present in table t1 two times so it has two keys. How this problems can be solved? I tried to use: insert into t3 (t1id,t2id) values ((select id from t1 where name like 'x'=ANY(select id from t1 where name like 'x')),(select max(t2id) from t2)); however then there was a message:ERROR 1048 (23000): Column 'id' cannot be null because it is t1 primary key? Are there any ways of dealing with this problem?
Inserting Data Into A Table
insert into enrolls (section_id,total) values(324,max(sub) +.1); how can I make it work so the value for the total of this entry is the max of the sub column from the same table + .1?
Inserting Dynamic Data?
In the connectorJ example: stmt.executeUpdate( "INSERT INTO autoIncTutorial (dataField) " + "values ('Can I Get the Auto Increment Field?')", Statement.RETURN_GENERATED_KEYS); it works perfectly fine for static text. I can't seem to figure out a way to get it to accept dynamic data (from java beans etc) i.e. mybean.getA(), myBean.getDate for example: "values ('Can I Get the Auto Increment Field?'" + getA() ) gives me a compilation error. What's the proper syntax?
Inserting VB Data Into MySQL Using Ado
I am trying to insert some data into a MySQL db, with Visual Basic by using ADO. I can select data without a problem using adorecordset.open, to insert would I use adorecordset.addnew? if anybody has done this and has a sample script I would be very happy to check it out.
Problem In Inserting Data
I have a table with 8 fields ,,in which one PK and two indices ,, now I want to insert a new data , and to avoid dublication I want to check the two indices together IF they are equal to the similar fields in the coming data , I want to remove the existed or update it completely (changing all the fields) , so I tried using INSERT.....ON DUBLICATE UPDATE .. it will check only the PK or one index then give you the chance to update one field ..(that what I understood about it). another way is , using REPLACE , but also it only checks the PK ... I dont want to check the PK.. it is only (AI) auto increment number HOw to do it saying like this : INSERT INTO mytable (A,B,C,D) VALUES (A1,B1,C1,D1) and IF the stored A == the coming A AND the stored C==the coming C THEN delete the existed or dont insert this row
Replace Values In One Column All Rows
I need to replace all values in one column with the values from another column - example: I want to replace all the values in the Username Column with all the values in the EmailAddress column - all rows to be effected.
Entering Dynamic Values Into Rows?
Is it possible when entering values into a table, to enter a string value from one column into another column in the same row in the same insert command? Is it possible in the insert command, to call some predefined function to get the appropriate value for the 3rd column? In calling this function, i would use the value from the 2nd column as the argument. this (theoretically), could look somthing like this: INSERT into IMAGES values (NULL, 'images/image_01.jpg', 'func(col2.value)'); the function would be somthing like: (excuse syntax, i haven't learned MySQL functions yet, but just to give an idea) function func(string s) { select str = s select str = SELECT REPLACE(str, '.jpg', ''); select str = SELECT SUBSTRING(str, 8); return str; } The resulting inputted values would then be: (NULL, 'images/image_01.jpg', 'image_01'); If this were possible, it would save me a LOT of uneccesary typing, as for each insert command, i'd only have to change the path name for col 2 and not type the name for col 3.
Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc). When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query. I tried it under phpmyadmin, and once again I get zero rows. Why do I get inconsistent results? Here's the query:
|
TRACKER
