Query To Insert 2 Foreign Key Values In Table
I want to insert 2 foreign key values along with some other value into table ....
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
INSERT ID From Foreign Key Table
I have a form used to populate 2 tables: Provider and Contacts. Provider has a foriegn key column - Contact1ID - which has a foreign key relationship with the prmary key - ID - in the Contacts table. The form submits data to Contacts then Provider but I need the ID generated by the Contacts submission to be entered into the Contact1ID column in the Provider table. If I use this: INSERT INTO Provider(Contact1ID) SELECT ID FROM Contacts WHERE FirstName='$contact1{FirstName}' AND Surname='$contact1{Surname}' .... all I get is a new row. UPDATE doesn't seem to allow SELECT to grab the new ID either. Any ideas?
Mutli Table INSERT With Foreign Keys
I want to add a new row to one table and then add an other row to a second table where one column is a foreign key to the first table. The first table´s primary key is set to auto increasment and I wounder if there is a simple way to find out the primary key´s "auto-set-value" from the first INSERT so I can use it when I fire the second INSERT? example: INSERT INTO tableA VALUES('','Mike','Male') INSERT INTO tableB VALUES('','Boston','?AboveInsertPrimeKey?')
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?
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?
INSERT INTO Table (vars) VALUES(vals)
I am trying to use the following command: INSERT INTO sr_main (originator,symreq) VALUES (A.Person,269); Whenever I enter the command above I get the following error: ERROR 1054 (42S22): Unknown column 'A.Person' in 'field list' I am confused because the error refers to my _value_ 'A.Person' as a column!? Do string values need to be surrounded with quote marks of some type? I have already tried the obvious (to me) quote markers, e.g. ", ' The data is accepted if I use this command: INSERT INTO sr_main (originator) VALUES (269); I actually have a lot more fields and values than shown, and ideally would like to use the command type: INSERT INTO tablename (field1,field2,field3) VALUES (1,2,3) Code:
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 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), .....
1 Simply Query Problem. Please Help With Insert Into 1 Table From Another Table
I have Table A that has some records already in and then I have Table B that does a few things and gets updated regularly. With Table A, I want to get it updated every 8 hours or so with new data that has been inserted into Table B. I will do this using the Cron. But what would be the best insert query to use so that it does the process really quick. The query that I have so far is: Quote: mysql_query("INSERT INTO table a (id, title, descrip) SELECT id, title, descrip FROM table b where app=1"); With the above query the id's match in both tables. But I only want the new records to be inserted into Table A where app=1 in Table B and the row is not already in Table A. How can I add to the query so that it does this.
Need To Use Same Table In Subquery As In Insert Query
I have this table called "testtab": ID | value ---------- 1 | 8 2 | 15 3 | 5 Field ID is INT autoincrement as usual for ID field, field value is common INT. I need to insert new record to it. The inserted value should be equal to the highest value +1. I tried to do something like this: INSERT INTO testtab SET value=(select max(value) from testtab)+1 Mysql threw this error: You can't specify target table for update in FROM clause I searched internet and found that I cannot use the same table in a subqery of insert/delete/update query. So my question is: Is here some workaround for this? I know I could use two queries - one to find the maximum value and second to insert the value+1 into DB, but I would like to do it in a single query.
Orderby Foreign Key Values
I seem to be completely clueless on how to order my results by foreign key values. e.g. I have an auction database and I want to order the auction by the current bid (Bids are stored relationally using foreign keys) right now I'm trying Code: ORDERBY id IN (SELECT auction_id FROM bids ORDERBY bid_amount DESC) I know this is sooo wrong, but I'm in the dark here, any help will be thanked by 10 million!
Foreign Keys With Null Values
The scenario: I have a number of customers and suppliers, and often more than one contact at each (ie in the case of the suppliers there might be an account manager, technician, director etc - each with their own contact details but sharing the same address) I am wondering what is best practice - to have a CUSTOMER table and a SUPPLIER table and one CONTACT table, with two foreign keys in the CONTACT table referencing the CUSTOMER table and the SUPPLIER table (hence one or other of the foreign keys will be null), or combine the CUSTOMER table and the SUPPLIER table into one ORGANISATION table with a TYPE field to determine whether the organisation is a customer or a supplier. In the case of the latter, the CONTACT table will have just one foreign key referencing the ORGANISATION table, therefore no null values will exist in the Foreign Key field... Any thoughts? Does it matter if foreign keys contain a null value?
Query (select/insert/update)Slows When Table Grows
I have a mysql database where tables are very simple. An example table will look like this: create table myTable( token varchar(255) not null primary key, token_count int default 1, frequency int ) type=INNODB; A java program populates this table. The program reads from files (in batch mode) and inserts into myTable with following logic: (1) See if the String to be inserted is present in myTable.(Uses SELECT) (2) If not present the insert into myTable (uses INSERT) (3) If yes update the table after adding the current frequency (USES UPDATE) After processing each file issue COMMIT. This goes on well for some time - but after the Table grows over 100,000 rows the process slows down considerably. The Strings I 'm handling is very large and I'm not sure how well Mysql behaves with very large String as primary Key. My select and update statements are all based upon primary key(token in this case) - so question of setting index doesn't arise. Even then I have set index on token field - but it has degraded the performance. Also I have set the query_cache_size to 1MB even then it is not enhancing the performance. A sample file has almost 30,00 to 50,000 lines. Each line having a string + blank + frequency. After processing first few files - the Java program takes large time to process a single file - and subsequently the processing time increases exponentially.(as the tabel grows in size it slows down).
Using INSERT With Foreign Keys, Possible?
How do I insert data into two tables without having to query a primary key? One table is a child of another table. Using the tables below, I would like to do something like this: INSERT INTO House (Street, City,State, Zip) VALUES ("foo","foo","foo","12345"); INSERT INTO HouseSale (HouseID,Price,Data) VALUES (????,"100000","2006-01-01"); ????= missing command Code:
Insert With An Auto-foreign Key Look Up
Say I have two tables... call the first ini_section, and the second ini_values They look like CODEini_section (pk)ini_section_id ini_section_name and ini_value (pk)ini_value_id (fk)ini_section_id ini_key ini_value
Error 1005 When Creating Foreign Keys (TINYINT Data Values)
I'v tried to isolate the problem, and it seems that with these two tables (tbRoles and tbParticipants) i can't create a foreign key with a TINYINT data type, i.e. if i change the data type from `RoleID` TINYINT to, say, `RoleID` VARCHAR(4), i don't get any error. Code:
Best Practice For Insert With Foreign Key Replacement
Am migrating some data from one table structure to another. Having trouble with an insert, which appears never to complete. Server is a local XP machine, P4 1.3, 1G memory, plenty of disk space, v5.0.27-community. Have tables A, B, C, and D, all InnoDB. Row Counts: A, 16K B, 250 C, 13K D, Zero (To Be Filled With Eventual Insert Query) A.f1 = A's primary index key (will be new foreign key for D) A.f2 = C's old foreign key B.f1 = B's primary index key (will be new foreign key for D) B.f2 = a text field C = the old data, to be inserted into D C.f1 = the data's old foreign key C.f2 = a text field which matches to B.f2 C has multiple rows containing the various old foreign keys Upon successful completion of the insert, for each row: D.f1 will be A.f1 (new foreign key) D.f2 will be B.f1 (new foreign key) D's other fields will be filled from corresponding fields in C In plain english, I want to do an insert, grabbing most of the fields from C and copying them over to D, but for D's field 1, instead of C's old foreign key (c.f1) I want to use A's new primary index key (a.f1), and for D's field 2, instead of C's old text field (c.f2) I want to use B's primary index key (b.f1). I would end up with the same number of rows in C & D, just those two field's values would be different. Code:
Insert Using Foreign Keys And Other Fields
I am trying to create SQL commands to initialize a database with several tables tied together using foreign keys. Table 1: id1 int primary key auto_increment state varchar Table 2: id2 int primary key auto_increment fk1 points to table 1, id1 city varchar So I can create two SQL commands: INSERT INTO Table1 (state) VALUES ('Illinios'), ('Virginia'), ("Massachusetts'); INSERT INTO Table2 (city, fk1) VALUES ('Springfield', 1), ('Springfield', 2), ('Richmond', 2), ('Springfield', 3); My problem is that if I create a new dataset using the same two commands, but add a state at the beginning of the file (or remove one), the keys change, and the 2nd insert statement will associate the cities with the wrong state, or fail. I would like the second statement to be something like: INSERT INTO Table2 (city, fk1) VALUES ('Springfield', SELECT id1 FROM Table1 WHERE state='Illinois'); or something like that. Is there any way for mySql to do this?
How To Randomly Insert Values?
Is there a statement that can randomly insert values into certain fields. For example let's say I have 1000 records and I want to randomly insert the values 10, 20, 30 into certain fields.
INSERT Values From SELECT
This works: INSERT INTO content (group_id) SELECT max(id)+1 FROM content; But how do I enter into more than one field? This doesn't work. INSERT INTO content (group_id, content_id) (SELECT max(id)+1 FROM content), 55; OR INSERT INTO content (group_id, content_id) VALUES ((SELECT max(id)+1 FROM content), 11); You can't specify target table 'content' for update in FROM clause
Insert With Both Values And Select
I need to insert multiple values in a table I use an insertstatement like this one insert into table(name, jobID) select 'Jon', id from jobs where name = 'programmer'; However, if I have a value before the condition like insert into table (jobID, name) select id from jobs where name='programmer', 'jon2'; it does not work and I need such a statement to work cuz I get different data with a lot of fields and I do not know in advance which will be fields with have direct values and hich will ghave to get data from other tables
Insert A String Of Values
as you will see iam new to php/mysql and desparately require some help. My problem is as follows - i need to enter a string of values from a list or selection of checkboxes into ONE field. Iam using dreamweaver and although it has the option for multiple entries it only inputs the first selected in a list. all i need it to do is be able to input for example beach facilities - toilets, slipway, lifeguard into one field from a list of facilities. i don't need to separate them at a later date as i will display them just as above. Your help will be greatly appreciated as it is a reg charity based website for marine wildlife that iam trying to construct.
INSERT (cols) VALUES (...), (...)
INSERT INTO labResults (labType, labValueAM, labValuePM, retest, labTest, sampleDate, RxNumber) VALUES ('saliva', '0.00', NULL, NULL, 'Estriol', '2006-02-24', '8888888') ON DUPLICATE KEY UPDATE (labType, labValueAM, labValuePM, retest, labTest, sampleDate, RxNumber) VALUES ('saliva', '0.00', NULL, NULL, 'Estriol', '2006-02-24', '8888888'); That's what I'm trying to do, but I get a syntax error. What's wrong?
INSERT Values In Three Tables Using ID Column
how to insert values in different tables? Table t1 got a column named "id" and this value should appear in t2.t1_id In other words: How to fill all columns in all tables with one INSERT / UPDATE ? t1 id bigint autoincrement name varchar() bla bla t1 id ....
How To Insert And Update Multiple Values In One Record
How do I insert and update multiple values in one record. e.g. 1. How do I insert "dogs,cats, snakes" in one record 2. If I have "dogs, cats, snakes" in one record and I want to update it and add 'pigs' to make it "dogs, cats, snakes, pigs", how do I do that?
How To Insert New Records Based On Previous Record Values?
I have TABLE: year value It has for example, year, code, vlaue 2007, 33, 4883 2006, 33, 4883 2007, 32, 4883 2006, 32, 4883 2008, 31, 4883 2007, 31, 4883 2006, 31, 4883 I want to insert new records for for 2008 for all codes that do not already have a record for 2008 and increase the value for the 2008 record by 3%. My several attempts have led to this but which does not work. UPDATE TABLE a SET a.value = b.value * 1.05 WHERE b.year = 2007 AND code NOT IN (SELECT code FROM TABLE b WHERE year =2008 AND value !=0)
How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?
Foreign Key Value In SQL Query...?
Currently toying with this cart script... http://www.zend.com/zend/spotlight/p...ping-cart4.php At one stage when I get the contents of the cart this query: PHP SELECT inventory.product, inventory.price, shopping.quantity, inventory.description, SUM(inventory.price*shopping.quantity) as subtotal SUM(subtotal) as total FROM shopping, inventory WHERE session='SESSID' AND shopping.product = inventory.product GROUP BY shopping.product is generated by this: PHP function get_contents() { $count = 0; $query = "SELECT ".$this->inv_table.".product," . $this->inv_table.".price," . $this->cart_table.".quantity,". $this->inv_table.".description, SUM(".$this->inv_table.".price*" . $this->cart_table.quantity .") as subtotal, SUM(subtotal) as total FROM ".$this->cart_table.",".$this->inv_table." WHERE session='".$this->cart_id."' AND ".$this->cart_table.".product = ". $this->inv_table.".product GROUP BY ". $this->cart_table.".product"; $result = mysql_query($query, $this->dblink); for($count = 0; array_push($contents, mysql_fetch_array($result)); $count++); $contents['items'] = $count; return $contents; } My setup is a little different in that "inventory.price" is infact a foreign key to a "pricebands" table with 11 different price bands. How can i get the correct price from my "pricebands" table within the MYSQL query?? In essence something like this... PHP SELECT inventory.product, (SELECT pricebands.bndPrice WHERE inventory.price = pricebands.bndID) AS inventory.price, shopping.quantity, inventory.description, SUM(inventory.price*shopping.quantity) as subtotal SUM(subtotal) as total FROM shopping, inventory WHERE session='SESSID' AND shopping.product = inventory.product GROUP BY shopping.product
Query Problems - Foreign Key
I want to be able to use an SQL statement to: 1)First of all find the last entered record in a table called cms_stories THEN 2)Once it has found this record it then grabs the story_id from the cms_stories table 3)Then it looks in the cms_images table to match (equal to) the story_id against the cms_stories table and output the path entered in the main_dir column. Now I tried the below, which I thought would work, but it says that the statement is ambiguous. PHP <? $SQL = "SELECT * FROM cms_stories, cms_pictures WHERE story_id = '$story_id' AND section = 'news' ORDER BY story_id DESC LIMIT 1"; $result = mysql_query($SQL) OR die(mysql_error()); $row = mysql_fetch_array($result, MYSQL_ASSOC); echo '<img src="new_site/pictures/big/'. $row['story_id'] .'">' ?>
SUM Of Two Foreign Table Fields
I've a project that has many timesheets (time) and many expenses (money) I'm trying to work out the cost of the project base on a set amount per hour for timesheets plus expenses. A project may not always have expenses. A project has zero or many timesheets A project has zero or many expenses I need an SQL statement to return total time for a project and total expenses so i can do my calculations then in php. I'd prefer do this in one SQL statement. What i have so far: SELECT project.name, SUM(timesheet.time) AS time, SUM(expenses.amount) AS expense FROM project, timesheet, expenses WHERE project.id = timesheet.project_id AND project.id = expenses.project_id GROUP by project.id ==TABLES== PROJECT id PK name TIMESHEET id PK project_id FK time EXPENSES id PK project_id FK amount This will work fine for all projects that have both at least one expense and one timesheet. But if they are missing one then it returns no row for that project and therefore i can't display the cost. There are other pieces of data but these are the only important ones. I can do it in two different SQL statements but i really think there has to be a way to do it in one.
Altering My Table To Add Foreign Key
i have two tables, cars and people. cars: make model engine people: name address sex what i would like to do is alter my table to create a foreign key 'MAKE' in the people table. i dont know the syntax to do this.
Which Table Is Foreign Key Constraint On
When trying to delete a record from a table, I receive the message "[root@localhost:3306] ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails". I know there is a foreign key, but don't know what table it is on. Is there an easy way to find out what table the foreign key is in that is restricting the record from being deleted.
How To Make One Table Has Two Foreign Keys
i just start to use mysql and i want to know how to write the command to make two fields as a foreign keys For Example, table 1 school_code (PK) School_room Bldg_code Office_room (FK) Prof_id (FK) how can i write this command using constraint?
Associative Table Or A Directly Foreign Key
I am working with hibernate and i want to map my Objects to tables structre. There is two ways to map relation: 1. with associative table 2. directly foreign key (i mean without associative table) Can you please explain what are the advantages for each way?
Cannot Modify Table To Create A New Foreign Key
I used this syntax: ALTER TABLE table1 CHANGE field1 field1 char(60) NOT NULL REFERENCES table2(field2) field1 is INDEX for table1 field2 is PRIMARY KEY for table2 table1 and table are InnoDB type I'm getting this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES table2(field2)' at line 1 What's the problem ? I followed MySQL Reference Manual to create the query, it' supposed to work.
Adding A Foreign Key Into An Existing Table
May I know how should I do that? The SQL statement should be: CREATE TABLE USER_SYSTEM_SECURITY ( USERID VARCHAR(32), HOSTID VARCHAR(32), HOME_DIRECTORY VARCHAR(32) NOT NULL, ACCESS_RIGHTS INT(3) NOT NULL, PASSWORD_EXPIRY_DATE DATE, ROLES VARCHAR(32) NOT NULL, PRIMARY KEY (USERID, HOSTID), FOREIGN KEY (USERID) REFERENCES MEMBER (USERID) ON DELETE CASCADE, FOREIGN KEY (HOSTID) REFERENCES SYSTEM (HOSTID) ON DELETE CASCADE); But I forgot to add in the last row during the table creation.
Foreign Key Relations In MyISAM Table
I am trying to setup a database with 2 tables 'Items' and 'Categories'. There descriptions are: Items(itemID,itemCategory,itemDescription) Categories(Category) I have added each of the databases, but have tried using the following to create a Foreign Key Relationship, but this doesn't appear to work. --- ALTER TABLE Items add CONSTRAINT fk_categories FOREIGN KEY (itemCategory) REFERENCES Categories(Category) ON DELETE CASCADE ON UPDATE CASCADE; --- Im running on phpMyAdmin 2.3.2 and MySQL 3.23.58. Both Tables are MyISAM, i have read abit about INNODB but my host doens't give the option for that database type. The only other options are Heap, BerkleyDB, ISAM and Merge. Im wondering if anyone can help me to work out how to create the relationship and where i can view it. After using the above, it accepts the code in the SQL window, but when a Category is changed in Categories, the Items table does not update.
Multi Field In Same Table Reference To Same Foreign Key
I am having trouble to come up with correct query, could someone give me a hand? I have created a table which contains several fields (SampleUser_ID, TestingUser_ID, Report_User_ID) and all of these fields reference to same User Talbe's primary key - user-ID. It works fine when I retrieve data only with Sample person name (as following) SELECT Resulttable.ID, Resulttable.data, lk_operators.UserName as 'SampleName' FROM lk_operators RIGHT JOIN ResulttableON lk_operators.User_ID = Resulttable.SampleUser_Id But I having problem to display all officer's name who work on the different task for the same sample. eg: Result.ID, SamplePerson_name, Testing_person_name, Report_person_name, Result.data I have try join on top of join without any luck. Can someone point me to the right direction?
Can't Add Foreign Key Contraint While Referencing Table Has Contents
using MySQL administrator to add foreign key constraint as such: ALTER TABLE `testhibernate`.`city` ADD CONSTRAINT `FK_countryid123123` FOREIGN KEY `FK_countryid123123` (`countryId`) REFERENCES `countries` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT; when the country-table contains data this gives: "MySQL Error number 1005 Can't create table ... (errno: 150)" when I delete the data from country-table the command is executed succesfully. Am I correct in thinking that it's not possible to lay contraints on tables while they contain data (in this way)?
Table Design, Normalization, Foreign Keys
In building a MySQL database what are the best practices to optimize speed and normalize? This is a food management database. Tables are such as item, manufacturer, brand, vendor, store location, nutrition facts, price, store dept, packaging, item type. And a few others. Given: 1. Tables are concise and have keys on all relevant search fields. 2. All tables have a prime, integer id field. (First field in table.) 3. Any repeating data field will be normalized. (Except for a few very minor areas.) Questions: 1. Use where field=field clauses to link the various tables only or mostly? 2. Use joins? 3. Use foreign field requirements to insure links and data completion? 4. Use triggers & procedures to do (3). 5. Have application programmers enforce the business logic? (Not really favored.) 6. Which approach is fastest? Where clauses to join, join clauses? Does it matter? Any recommended design tools? Am using MySQL Browser & Administrator.
Distinct Values In Mysql Query
I have mysql query but i am not sure about it,bcoz it return many rows.. I want like distinct values from three tables with ic number. Which is posted by user... "SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,loguser.staffcode, DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1, DATE_FORMAT(loguser.creationdate, '%d-%m-%Y %H:%i:%s')As formatteddate FROM dealerdtl,loguser,access where access.icnumber=loguser.icnumber AND loguser.icnumber = '$custic'"; This query return many rows i dont know why, i want only one row which is inserted by the user as customer id.
Reference To Already Retrieved Values In Query
As a simple example, say there is table 'namelist' with column 'names' as char(20). I would like to do something akin to: select namelist.names as mynames, left(mynames,2) as initials; In this example, I could just do left(namelist.names,2), but in more complex cases a value retrieved may have had a more complex logic behind it, e.g., if a bunch of nested if() statements. It would seem logical that if a value is already retrieved then I should be able to refer to it within the same query, assuming that the original value does not need to be recalculated. I know I could use a view to do this, but it adds another layer of complexity, and I am not sure that the values would not be recalculated each time the value is needed. If someone could point out to me the correct terminology for this kind of thing I believe I should then be able to look up the information myself. My efforts to find this information on google and the mysql website were unsuccessful.
|
TRACKER
