Inserting Values From 1 Table Into Another Table
I have 2 tables: A and B
Table A contains fields u, x and y
Table B contains fields u and z
Table A has all possible variations of u.
Table B has some of the varaiations of u in it.
My problem is that I'm trying to insert the z-values from Table B into Table A's y field and link them up based on the u-values.
I'm running into problems like truncating at row 0.
I've tried the following queries:
update TableA set y =(select TableB.z from TableB where TableA.u=TableB.u)
update TableA,TableB set TableA.y =(select TableB.z from TableB where TableA.u=TableB.u)
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
INSERTing Into A Table, With Values From Another Table
I'm programming a shopping cart application, where the products are categorized into different lines. I have a table that defines the different product lines, and now want to populate the product table. However, the text file with the products in it, which I will use to populate the products table, uses the line NAME for each product, when I wish to use the line ID instead. To get the line ID, I would need to get the value from the line table. My question is: is it possible to populate the products table with data from the products text file, as well as a column from the lines table? That is, the INSERT (or LOAD DATA) statement will insert column values from the text file, but the line id from the lines table instead of the line name found in the file (WHERE line name = line name in the lines table). I'm still not sure whether to use an INSERT, and write PHP code to go through the text file, or to use LOAD DATA for this task, because I don't know which statement would support what I'm looking for.
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 Items (# Unknown) Into A Table Using Primary Id From Master Table
In my database I have a master table for products using a auto-num id column. One product may have several purchase options (items) which have to be entered into another table. To enter a new product and items I use a form that gathers the information for the master product and one item. This information is then inserted into the database. For the items I use mysql_insert_id() to get the id into the items table. My challenge is getting this same id for additional items. Once the user has input the initial product and first item they hit a submit button to call a form for adding any additional items, and this is repeated until all items for that product are entered. The new items are inserted into the database okay, but always with an id of 0. The mysql_insert_id() no longer works. Is there a way to pass this id from the previous insertion to the new one by passing a variable? or echoing input type"hidden"? Or even better yet, Im wondering if I can ask the user at the time of entering information for the product how many items that particular product has and then bring up that many form fields to enter multiple items? Can this be done using something like an incremented loop to call form fields?
Insert Table Values From Another Table
I have two tables: Users and metadata Users: id userid username Metadata: id userid value I would like to extract a list of the userid's from the users table and enter them into the metadata table, with an additional value. Example: I want to take all of the userid's from the User table and insert them into the Metadata table, and make the value="one" for each entry into the Metadata table.
Update One Table Value With Values From Another Table
I am trying to update one table value with values from another table, and I cannot get it to work. What am I doing wrong? This is my SQL-command: UPDATE tabel1 SET tabel1.name=tabel2.name WHERE tabel1.ID=tabel2.ID
Inserting Value From One Table To Another
In my database called 'asterisk', I have a table named 'sip'. It has the fields: id,keyword,data,flags -- Primarykeys:id,keyword I have another table named 'userdata'. It has the fields: id(autoincrement),account,username,secret,Mobile,SIP,MSN -- PK: id Now, in table sip, id will have values '0' for the first few records, say 13 records. After that, it must have the value '1' for the next 3 records, value '2' for the next 3 more records and so on.. So value of id shd b incrmented by 1 every 3 records after id=0. Now, the 3 records tat wil b entered into sip table are: Code:
Inserting Into Table
I'm having some trouble inserting variables into my database. All the variables I'm trying to insert have been declared above this part, used atleast once, so they are correct. But when I view my table, the entires are not there. code.. The lastName string is "O'Brien." I took out the apostrophe by hardcoding in another name, but it is still not being inserted. The last empty var is for the ID which autoincrements.
Inserting Date Into Table
I am wanting to insert a date into a table for the date something was inputted into the database. Which would be the best (or only) way to do that? 1. DATE function in MySQL 2. date() function in PHP and submit the output into MySQL If there are any other ways, let me know, as I would like this to be the best possible way and work all the time.
Inserting ENUM Value Into TABLE? How?
i'm trying to insert an ENUM value into table, the field column inside a table is also set to ENUm. $query = "INSERT INTO users (activated) VALUES (Ƈ')"; mysql_query($query) or die('Error, insert query failed');
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 Records From Another Table
i am using the following SQL to insert records into Summary table from another table but i keep getting a syntax error. When i run teh sqls individually, they work ok... any thought on how i can get this done? INSERT INTO tSummary (ssnumber,unitid,projectid,tenant_lname,tenant_fname) VALUES(select ssnumber,unitid,projectid,tenant_lname, tenant_fname from tExternalData) The error that i get follows: [admin] ERROR 1064: You have an error in your SQL syntax near 'select ssnumber,unitid,projectid,tenant_lname, tenant_fname from tExternalData)' at line 3
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?
Error Inserting Into Table
(MySQL 4.1.7 using phpMyAdmin 2.6.0-pl3) Here's a file called info.csv: "e-mail","Subscribe_to_Newsletter ","" "ajak@myway.com","on","" "anthony_knight@hotmail.com","off","" I created a table called mail, with 2 columns (e-mail Subscribe_to_Newsletter) Then ran this query: INSERT INTO mail (email, Subscribe_to_Newsletter) VALUES ("ajak@myway.com","on","" "anthony_knight@hotmail.com","off",""); But got this error message: Error SQL-query: INSERT INTO mail( email, Subscribe_to_Newsletter ) VALUES ( "ajak@myway.com", "on", "" "anthony_knight@hotmail.com", "off", "" ) MySQL said: Documentation #1136 - Column count doesn't match value count at row 1.
Inserting Date In To The Table
my table structure is temp( name varchar(10), da date); it is only accepting dates in the format 'yyyy-mm-dd' ex: 2005-02-16 and for any other vaules it is inserting 0000-00-00.
Inserting Unicode In Table
I have created a table as follows - CREATE TABLE verb_table (verb VARCHAR(25), briathar VARCHAR(25), conjugation VARCHAR(5), past_tense VARCHAR(25), aimsir_chaite VARCHAR(25)) TYPE=InnoDB CHARACTER SET utf8; Now how do I insert unicode values into a database using the following format - insert INSERT INTO MyFirstTable ("To run","rith","1","ran","rith"); How do I escape a unicode value? For example, how do I insert a "long r" (u027c) in place of a normal r in the example above?
Inserting Charcter String Into Table
We are trying to insert a string of characters similar to N234ND into a table. Everytime we try this we get the 1054 Error code. Stating "Unknown column 'N' in 'field list'. The N relates to the first N in N234ND.
Error Inserting Into Temporary Table
I create a temporary table by using following statement: CREATE TEMPORARY TABLE ttable1( id tinyint( 4 ) , q decimal( 5, 0 ) ) TYPE = HEAP tryng insert a record by statement: INSERT INTO ttable1 VALUES ('1','1') return the following: #1146 - Table 'rail.ttable1' doesn't exist How can I insert in my temp table?
Inserting Variables In MySQL Table!
Have you guys tried inserting variables in mySQL tables? Do I have to use 'quote' as we had been doing to insert strings? mysql> INSERT INTO occurrence (word_id,page_id) VALUES ($word_id,$page_id); ERROR 1054: Unknown column '$word_id' in 'field list'
Inserting A Record In Table That Has Auto_Increment
I'm trying to insert a row into a table using data from variables with values from an excel sheet. the problem I'm having is that the table I'm inserting them into has an Auto_increment field and I can't get the code right for it to work. Here is a sample of what I have: Code:
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?
Trouble Inserting Ph Variable Containg A Number Into Sql Table
I select an id from one table to use in another so I can have them related to one nother. My code was working yesterday, now I get this error atabase error storing file: MySQL server has gone away--------and sometimes I get the database store error and my version is wrong, it points to the filename=". Do I use single, double or no quotes when I have a variable that contains a number and want to insert that variable into MySQL? This is part of the code if this helps: //get member id to insert into file store table $get_id = "SELECT DISTINCT id FROM member WHERE soc_num = '{$_SESSION['soc']}'"; $get_id_res = mysql_query($get_id) or die("Couldn't get id"); #90 $id2 = mysql_fetch_array($get_id_res, MYSQL_ASSOC) or die("Could'nt get id2"); $id2['id'] = $member_id; // Create the SQL query. $sql = "INSERT INTO filestore SET member_id = $member_id, filename = '$uploadname', mimetype = '$uploadtype', description = '$uploaddesc', #100 filedata = '$filedata'";
Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables: HOUSE house_ID address OWNER owner_ID name telephone... HOUSE_OWNER_JOIN ? CODE_VIOLATION_HISTORY house_ID violation_ID violationStatement ... My goal is to be able to track code violations of the house PER owner. For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like: House 1009283 Address Past history (link to the following) House History 2001-01-04 Owner: John Smith Code Violation: Gutter issue 1999-06-01 Owner: John Smith Code Violation: Faulty Steps 1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks 1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help. Option 1: Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way: HOUSE_OWNER_JOIN houseID ownerID dateOwnershipBegan dateOwnershipEnded then I could look up all code violations by date and associate them with their rightful owner. ================================================== Option 2: Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID: HOUSE_OWNER_JOIN house_owner_ID houseID ownerID dateOwnershipBegan dateOwnershipEnded CODE_VIOLATION_HISTORY house_owner_ID violationStatement ...
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.
Add Values To Table
I have a table tblMailingList. It has 3 columns; Name, EmailAdd, AutoID. I am using mysql> INSERT INTO pet -> VALUES ('name1','u@me.com'); but I am unsure as to what to do for the AutoID field. If I use the example given, the error message says that the number of fields is different. How do I get this info into my table?
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.
INSERT Values From One Table To Another
In table A i have a.id and name and in table B, i have b.id, a.id(FK) and owns. Table A -------------- a.id | name | -------------- 12 | James | 13 | Paul | 14 | Tom | 15 | Andy | Table B -------------- b.id | a.id | owns | -------------------- 1 | 12 | Car | 2 | 12 | Bike | 3 | 13 | PC | Now what i want is to add two new names to table A which will be Tom and Andy and have the same data in table B for Tom and Andy with just different b.id (PK). How can i insert this new data in table B using insert?
Update A Table With Values In Another
I have the following table tableA column_a column_x column_y column_z tableB column_x column_y column_z How do I update the rows of tableA that match rows of tableB (all rows of tableB are unique) What I want to acheive is a check against tableA.column_x with tableB.column_x, if they match, then update the row in tableA.column_y with the value of tableB.column_y and tableA.column_z with tableB.column_z
Edit Values In Table Through GUI
I wonder which GUI tool I can use if I want to edit some values in a table? I can't find it in Query Browser. Do I really need to execute SQL to make a simple change to a field?
Update Table Values
I have a csv.txt file with contacts and their information in it. Then I also have a table in mysql with the same contacts and their various information. I need to merge these to so that if a field value in the database is NULL the value that is in the csv.txt file is inserted into the database field. I have been looking at the mySQL site on how to do this but have not been able to produce a working solution. Below is a more detailed example of what I am looking for. Code:
Returning Values That Are In One Table, But Not In Another
I'm looking for a query to return values which occur in one table, but not in the other. Say we have two tables (table_1, table_2) each with a column titled "appointments". I want a list of results for appointments which are in table_1, but not in table_2 I have tried the following, but all I get is a huge list of duplicate appointments which occur in both tables: SELECT table_1.appointments FROM table_1, table_2 WHERE table_1.appointments<>table_2.appointments; I've also tried 'NOT IN' for this but the result was just the same.
Editing Values In A Table
I just moved from SQL Server to MySQL and in the Enterprise Manager in SQL Server I could query the entire table and then modify the values with the table without writing an UPDATE statement. I cannot do that in one of my tables because the EDIT at the bottom is greyed out. There are other tables where I can make a modification to any value I want within the table.
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 .....
Generating A Table With Missing Values In SQL
I have the following table: +------+------+------+-----------+ |ID |GROUP |CODE |ANSWER | +------+------+------+-----------+ |1 |1 |001 |01-01-2003 | |2 |1 |002 |Yes | |3 |1 |003 |NA | |4 |1 |004 |297797 | |5 |1 |005 |0 | |6 |2 |002 |Yes | |7 |2 |004 |297852 | |8 |2 |005 |1 | +------+------+------+-----------+ This means that code 001 and 003 of group 2 are missing. Result of the query should be: +-------+-----------+-----------+-----------+-----------+--------+ |GROUP |ANSWER1 |ANSWER2 |ANSWER3 |ANSWER4 |ANSWER5 | +-------+-----------+-----------+-----------+-----------+--------+ |1 |01-01-2003 |Yes |NA |297797 |0 | |2 |NULL |Yes |NULL |297852 |1 | +-------+-----------+-----------+-----------+-----------+--------+ The missing values should be replaced by a NULL value in my resultset. Is this possible? I don't have any clue how my query should look like, can anyone help me and show me a solution?
Eliminating Double Values From A Table
I need to consolidate data from many adress databases, which i allready sucessfully put into one table1. As Newbie i am reviing the Manuals and tutorials, but in 2 days i learned a lot about the powerfull command, but not how to solve the following question: Is there a straight forward method to copy data from one table1 into the other table2, but not those rowse, which have same value in Fieldx. All my Update and INSERT SELECT experiments somehow created more then less data in the target table.
Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table. I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded. update boxes b inner join messages m on b.ctnnumber = m.ctnno set b.consigneerecv = m.CName, b.consigneerecvdate = m.DateRcv, b.phrecventered = "Y", b.PhilStatus = "delivered", b.prevreleasestatus = b.releasestatus, b.releasestatus = "delivered", b.PhilStatusDate = m.smsrecvdate, b.phdelprice = "0.00", b.phdelamt = "0.00", b.recvrelation = m.Relation, b.APRecventered = m.smsRecvDate where b.consigneerecv = '' or b.consigneerecv = 'NA' or b.consigneerecv is null; I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this: Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1), table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....
Insert Values Only If They Do Not Already Exist In Table
I want to insert some values from one table (A) into another table (b). However there are some values in table A which exist two times in table A. I only want to insert values one time. So If a certain field value also exist in some other row (same fieldname) then I don't want to insert it twice. It doesn't matter which of the two rows will be inserted. Can this be done with MySQL? Maybe with some Control Flow Functions?
Updating A Table To Increment Values
Question, if I want to update all values in a certain column to increment by one, what statement could I use? In quasi-MySQL, this is my pseudocode: UPDATE table SET fields=[previous_val+1] where [previous_val]>[my_val] Or do I HAVE to run a SELECT query, increment the value, and THEN Update it? I was just thinking there'd be an easier way.
Accepting Values As NULL When Not In Another Table
Is it possible to perform a SELECT query on multiple tables and simply accept one of the values as NULL if not in another table rather than the whole query failing? For instance, if "rec_id" exists in "table_1" for certain but it is uncertain if it exists in "table_2", is it possible to return the query with a variable assignment stating whether "rec_id" was or wasn't found in "table_2"? I am hoping I can do this without a LEFT OUTER JOIN as the query is fairly complex and involves five different tables. Whenever results are fetched I simply need to know if rec_id is or isn't in table_2 if it is found in table_1. As there are frequently over 10,000 result rows returned it wouldn't be sound to put this into a seperate loop that queries with each rec_id to see if it is in table_2. I have tried "SELECT table_1.rec_id, table_2.rec_id FROM table_1, table_2 WHERE table_2.rec_id=table_1.rec_id OR table_2.rec_id IS NULL" but this returns no records.
An Enum Field Using Values From Another Table
The first table, called genus, contains several fields, the important one in genus is name, and there are others about the individual plant types. The second table 'plants' needs one field for the genus name, so that I can link tables on this. If there a way to make a foreign key from the genus table into an enum field on the second? My idea is that then, with phpmyadmin or similar I can just select the genus name from a list when entering new plant data.
Renumber Mysql Table Values
There are 100000 rows in a table, I need to renumber them from 1,3,6,8,11,14,21,55,888,1000,9999...99999 to 1,2,3,4,5,6,7,8,9,10,11,12,13,14... etc I also need to update an associated table, so its going to need to lock the tables, find the lowest available index in table1, then update table1 index, and then update table2 to use the same index as well, then unlock tables What would be the procedure to do that?
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?
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.
Returning All Unique Values From A Table Field
I wrote a select that works fine but I am looking at it and thinking that the query might require mysql to scan EVERY row before giving me my answer. Lets pretend I have a table with fruits and want to return all the available colors of those fruits that I currently have in the database. Is this an efficient or horribly inefficient statement? "select color from fruits group by color" It seems to return what I want: fruits: ------ blue green yellow ... I am just wondering if it goes through EVERY row and groups them? If I have the table indexed by color am I allright?
|
TRACKER
