Insert values from separate tables. Please help.


Can someone please help me

I am simply trying to insert a value from one table into another table using a SELECT statement. I'm too new to MySQL however to understand what I am doing wrong.

My two tables, which are both contained in my database named "comp", with their associated columns are the following:

hr_people(id, first_name, last_name, national_id, cdate)

hr_jobs(id, hr_people_id, position, start_time, end_time, cdate)


I am trying to insert new rows into the the hr_jobs table by using values from the id column of hr_people table. I am doing something wrong however and I have a feeling it is something very simple but because I am new, I am simply overlooking it. The query which I thought would work is:


INSERT INTO comp.hr_jobs (id, hr_people_id, position, start_time, end_time, cdate) VALUES
(NULL,
SELECT id FROM comp.hr_people WHERE national_id LIKE 'TSIA-12544',
'CEO',
'1258437600',
'1262325600',
UNIX_TIMESTAMP()
)


Can anybody tell me what I am doing wrong with my select statement It would be greatly appreciated.
Posted On: Wednesday 24th of October 2012 01:21:35 AM Total Views:  154
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




inserting records exceeding integer data type length given

actually when iam creating table i have given column length 11 & auto increment. --------------------------------------------------------- CREATE TABLE IF NOT EXISTS `merchants` ( `merchant_id` int(2) NOT NULL auto_increment, `merchant_name` varchar(255) NOT NULL, PRIMARY KEY (`merchant_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Merchants Master Table' AUTO_INCREMENT=1 ; --------------------------------------------------------- Then i wriiten one program to insert records in to it, having loop 1 to 100000 records. its inserting...... There is no restriction in the integer range.. iam in full confusion, though iam having 3 years experience in php. i thought till now if we give 11 length, records will insert till 11 digits. please give reply asap.
VIEWS ON THIS POST

149

Posted on:

Sunday 21st October 2012
View Replies!

last insert id

how to get last insert id of an auto increment field.
VIEWS ON THIS POST

125

Posted on:

Sunday 21st October 2012
View Replies!

Complex select query with nested if constructs, query insert and query delete.

I have this mysql query with maybe clear comments. Please can you help me to correct it I think that it is a comprehensible code but it requires many corrections. and also forgive me for my bad english. this is my query with the commented code: Code MySQL: SELECT log_uname IF ( log_uname = 'new_username', /* if true that already exists but it is not a verified username */ ( SELECT flag_verified, log_uname /* nested if */ IF ( (TIMESTAMPDIFF(MINUTE, NOW(), SELECT date_registered FROM users WHERE flag_verified = 0 AND log_uname = 'new_username')
VIEWS ON THIS POST

139

Posted on:

Sunday 21st October 2012
View Replies!

insert into table is doesn't exist and update if it does?

I read on one of the threads sitepoint search brought up that I can do it with something like the "ON DUPLICATE KEY" or so but in my table using unique key is not possible. table `members` has fields `member_name`, `age`, `gender`. example entry is: Code MySQL: INSERT INTO `members` (`member_name`, `age`, `gender`) VALUES ('Tanya', '18', 'female') However, I want to do the following if 'Tanya' already exists, Code MySQL: UPDATE `members` SET `age`='18', `gender`='female' WHERE `member_name`='Tanya' . I can check if it exists via php and do appropriate action, but is there a way to do this through mysql
VIEWS ON THIS POST

121

Posted on:

Sunday 21st October 2012
View Replies!

insert into select question

I have a table that has usernames, I want to make a copy of the rows with a username but swap out the username with their mac address. This way their username could either be a mac address or a username. I have tried this (below), at first it complained about a duplicate id and id='0' fixed that and now UserName becomes a 0 in the database. Code: INSERT INTO radcheck SELECT id = '0' , UserName = 'MacAddress' , Attribute , op , Value FROM radcheck WHERE UserName = 'lorenww' I have to do this after they have signed up and only if their plan has MAC authentication when logging in. I'm hoping someone can help with this.
VIEWS ON THIS POST

180

Posted on:

Sunday 21st October 2012
View Replies!

insert of aggregated value becomes null?

When i do call this in console prompt I get the correct value, but when it comes to inserting it, it inserts a null value What do you think of that Code: INSERT INTO clist2 SELECT CEIL((o + m + p)/6)* Cost) as FinalCost ...
VIEWS ON THIS POST

250

Posted on:

Sunday 21st October 2012
View Replies!

Combining an insert and select?

Is it possible to combine a select and insert statement into one statement I would like to combine these two statements into a single database call. Code: SELECT tx.account_balance as last_balance FROM transactions AS tx INNER JOIN ( SELECT MAX( txn_id ) AS last_tran FROM transactions WHERE user_id =63 ) AS m ON tx.txn_id = m.last_tran INSERT INTO transactions (user, value) VALUES ($user, last_balance+$amount )
VIEWS ON THIS POST

97

Posted on:

Sunday 21st October 2012
View Replies!

insert into help with condition

hello, I have a table that stores related products. it is a simple table with 2 fields. the first is a product_id field and the second is an array of product_id's that are related to the product_id in the first field. I would like to write a 'insert into' that will query my main product table and find all the products that have a product_parent_id (child products) that matches a product_id from my related products table in the first field - and then adds a new record for each child product that also copies the array of product_id's from the second field in the related products table. some thing like: product tbl product_id | product_parent_id | product_name 2 | 0 | Color Reflector 3 | 2 | Color Ref 01 4 | 2 | Color Ref 01 5 | 2 | Color Ref 01 related products tbl product_id | product_relations 2 | 12,22,24,33,49 And what I would want my 'insert into' to accomplish is to add product_id's 3-5 to the related products table with the same product relations as their parent products, like: related products tbl updated product_id | product_relations 2 | 12,22,24,33,49 3 | 12,22,24,33,49 4 | 12,22,24,33,49 5 | 12,22,24,33,49
VIEWS ON THIS POST

108

Posted on:

Sunday 21st October 2012
View Replies!

Update query gives no result but insert query gives result in 1 transaction

, I have 2 queries which I run under one transaction but for some rare cases the update query give no result but there is no error generated but in all cases the insert query works. Part of the logs show the queries have been posted below. 080804 14:40:21 207 Connect root@localhost on mpcms4 207 Query SHOW VARIABLES 207 Query SHOW COLLATION 207 Query SET character_set_results=NULL 207 Init DB mpcms4 207 Query Select tblProduct.productTotalStock, tblProduct.productBooked, tblProduct.productCode, tblProduct.productName, tblProduct.productPrice, tblProduct.rrpMarkUpType, tblProduct.rrpMarkUpFixed, tblProduct.rrpMarkUpAmount, tblProduct.rrpMarkUpPercentage, tblProduct.productTotalAmount From tblProduct Where productID=1592 208 Connect root@localhost on mpcms4 208 Query SHOW VARIABLES 208 Query SHOW COLLATION 208 Query SET character_set_results=NULL 208 Init DB mpcms4 208 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 208 Query BEGIN 208 Query Update tblProduct Set tblProduct.productBooked = tblProduct.productBooked + 1 Where tblProduct.productID=1592 208 Query Insert into tblTempReceipt Set productType='Accessory', productID=1592, stockID ='6', stockSIQ ='1', employeeID = '9', tempReceiptTimeStamp='2008:08:04 02:40:21' 208 Query COMMIT 208 Quit 207 Quit 080804 14:51:50 214 Connect root@localhost on mpcms4 214 Query SHOW VARIABLES 214 Query SHOW COLLATION 214 Query SET character_set_results=NULL 214 Init DB mpcms4 214 Query Select tblProduct.productTotalStock, tblProduct.productBooked, tblProduct.productCode, tblProduct.productName, tblProduct.productPrice, tblProduct.rrpMarkUpType, tblProduct.rrpMarkUpFixed, tblProduct.rrpMarkUpAmount, tblProduct.rrpMarkUpPercentage, tblProduct.productTotalAmount From tblProduct Where productID=1068 215 Connect root@localhost on mpcms4 215 Query SHOW VARIABLES 215 Query SHOW COLLATION 215 Query SET character_set_results=NULL 215 Init DB mpcms4 215 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 215 Query BEGIN 215 Query Update tblProduct Set tblProduct.productBooked = tblProduct.productBooked + 1 Where tblProduct.productID=1068 215 Query Insert into tblTempReceipt Set productType='Accessory', productID=1068, stockID ='7', stockSIQ ='1', employeeID = '9', tempReceiptTimeStamp='2008:08:04 02:51:50' 215 Query COMMIT 215 Quit 214 Quit
VIEWS ON THIS POST

218

Posted on:

Sunday 21st October 2012
View Replies!

Joint insert form in phpmyadmin

I have to enter data across a number of tables and was wondering whether there is any way to join up the forms other than having to consistently skipping back and forth between tables to enter data
VIEWS ON THIS POST

153

Posted on:

Sunday 21st October 2012
View Replies!

insert if not exist

Hi when I need insert rows if a field not exist in database I I do like this : Code: $count = mysql_num_rows(mysql_query("select mail from useres where mail ='$mail'")); if ($count < 0) $query = mysql_query("insert into users set mail='$mail'") I mean a code with this algorithm : insert a row to database if one field not inserted at last and not exist ( if exist ignore )
VIEWS ON THIS POST

97

Posted on:

Sunday 21st October 2012
View Replies!

If entry exists update otherwise insert entry problems???

I want the user to be able to update what they are typing as they are doing that. If the entry exists it is suppose to update that entry not insert another, or overwrite another. Can someone please help me with this, as this just isn't working for me
VIEWS ON THIS POST

126

Posted on:

Sunday 21st October 2012
View Replies!

Get the id before inserting the record?

Probably not as complex as it sounds, but basically when someone fills out a form, I'd like to give that group of fields it's own id. So, it would have a group id, and then each item would have that group id associated with it. Is there a way to do this in one query
VIEWS ON THIS POST

110

Posted on:

Sunday 21st October 2012
View Replies!

update or insert sql

I am using this query to update tableone based upon the values in tabletwo. I really like this query because it works very fast. PHP Code: UPDATEtableonen,tabletwoc \tSETn.sec\t=c.sec, \t\t\tn.cat=c.cat,\t \t\t\tn.published=1 WHEREn.section=c.section \tANDn.category=c.category This works great however I cant seem to get it to insert or append if the data already exists. It just overwrites the data. My difficulty is that I need certain columns to be updated (overwriting the data) and other columns to be inserted or appeneded to (adding to the data). Is a stored procedure the best way of doing this Or, is there a way of adapting my sql to cater for this Cheers D
VIEWS ON THIS POST

114

Posted on:

Sunday 21st October 2012
View Replies!

insert or update if exist

, what is the sql statement that can insert or update the record if it exist.
VIEWS ON THIS POST

122

Posted on:

Monday 22nd October 2012
View Replies!

Problem inserting special characters

I am using PHP... I have a form, and one value is: Často, denně nebo obden when I echo the $_POST to the page, everything shows up as expected, but once I insert it into the database it converts to: asto, denn nebo obden However, another value is: U si nepamatuju and it gets inserted properly: U si nepamatuju Why do some characters get converted to question marks while others do not How do I make ALL of them work!
VIEWS ON THIS POST

175

Posted on:

Monday 22nd October 2012
View Replies!

Using Join vs transaction on multiple table select/insert/update

I am trying to decide on the approach to use for the customer part of a database. I will have a customer table, an address table and a credit card table. A record in the address table can be a billing address, a shipping address or both. A customer can have one or more billing addresses, zero or more shipping addresses and one or more credit card addresses. Each credit card record will have one billing address. The customer name may be different from the billing and/or shipping name. This is still in the design phases so any recommendations on table layout are welcome. Here are the present table layouts. CUSTOMER TABLE ----------------------------------------- FIELD TYPE COMMENT -------------- ------------------- ---------------------------------------- cid INT(10) UNSIGNED #customer ID email VARCHAR(64) PASSWORD BLOB PasswordSalt CHAR(5) FirstName VARCHAR(32) LastName VARCHAR(32) Phone VARCHAR(24) dbaid INT(10) UNSIGNED #defaultBillingAddressID dsaid INT(10) UNSIGNED #defaultShippingAddressID dccid INT(10) UNSIGNED #defaultCreditCardID emailFormat INT(10) UNSIGNED #0=html,1=plain TEXT receiveEmails TINYINT(1) UNSIGNED #0=yes,1=NO changePassword INT(1) #1=PASSWORD must be CHANGED ON NEXT login ADDRESS TABLE ---------------------------------------- FIELD TYPE COMMENT --------- ---------------- ------- aid INT(10) UNSIGNED #address ID cid INT(10) UNSIGNED #customer ID alias VARCHAR(15) FirstName VARCHAR(32) LastName VARCHAR(32) Company VARCHAR(50) Address1 VARCHAR(50) Address2 VARCHAR(50) City VARCHAR(50) State CHAR(2) Zip VARCHAR(10) Country VARCHAR(13) Phone VARCHAR(12) atype INT(10) UNSIGNED #0 = billing & Shipping address, 1 = billing only 2 = shipping only CREDIT CARD TABLE ------------------------------------------- FIELD TYPE COMMENT -------------- ------------ ------------------ ccid MEDIUMINT(6) #credit card id cid MEDIUMINT(6) #customer id aid MEDIUMINT(9) #billing address id nickname VARCHAR(20) CardHolderName VARCHAR(50) CardTypeID MEDIUMINT(6) CardNumber BLOB salt CHAR(5) ExpMonth VARCHAR(12) ExpYear VARCHAR(4) My questions are on how I should approach inserting, updating, deleting and selecting records in the table. Approach one is to use joins to do all the work in a single statement. Here is a sample Stored Procedure to get the customer and billing and shipping addresses. Adding the credit card to the select is a simple addition. CREATE PROCEDURE `procGetCustomerByIDEx`( pCid INT ) BEGIN SELECT c.*, ba.*, sa.* FROM customer c INNER JOIN address AS ba ON ( c.dbaid = ba.aid) INNER JOIN address AS sa ON ( c.dsaid = sa.aid) WHERE c.cid=pCid; END This will be slightly complicated in that the shipping addressid may be null in which case the billing address id is also the shipping address id. So the first approach is do do everything in a single statement. The statements may get a bit messy, especially if the shipping address changes from a combined billing/shipping address to separate ones or if a combined address is used. Approach 2 is to do things individually in a transaction with the typical BEGIN, COMMIT, ROLLBACK. The select/insert/delete/update will be done with individual statements. This will be easier if the logic gets murky and I can see it getting complicated. My MySQL skill set is probably medium Performance is not an issue. This is for a low volume server. Which approach would you use Let me know any other suggestions you may have.
VIEWS ON THIS POST

103

Posted on:

Wednesday 24th October 2012
View Replies!

Mysql import inserting only null values into table

, I took dump of a database 'new' with only one table 'backup' using the following command : mysqldump -u root -p -h localhost new --result-file=C:\Users\Administrator\Desktop\backup.sql When trying to restore using : mysqlimport -u root -p new C:\Users\Administrator\Desktop\backup.sql, it gave me an error mysqlimport: Error: 1366, Incorrect integer value: '-- MySQL dump 10.13 Distrib 5.5.24, for Win64 (x86)' for column 'id' at row 1, when using table: backup. I read somewhere to disable strict mode so i edited my.ini and commented out sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION". Now when I try to import, all that goes into the database table are null values. None of the data in the .sql file is acutally making it into the database. Please help. tnx
VIEWS ON THIS POST

258

Posted on:

Wednesday 24th October 2012
View Replies!

Option to insert data into mysql table

Hi all, Just reached the point where I can now create database tables with required number of fields using MAMP. My problem is I do not know how to insert data. I found this link tizag.com/mysqlTutorial/mysqlinsert.php which raises the following questions, 1. Is writing php code the only way to insert data into the table 2. If point 1 is true, does it mean that inserting data into the table is done only within the html codes 3. Is there any free software/add-on to insert data into the table TIA!
VIEWS ON THIS POST

210

Posted on:

Wednesday 24th October 2012
View Replies!

Help Needed for : Auto insert into Reference table

Hi Folk, I want to do one thing in MySQL.. I have one table sat 'user' given below : Mysql Code: Original - Mysql Code +------+--------+ | id | name | +------+--------+ | 1 | shariq | | 2 | david | +------+--------+
VIEWS ON THIS POST

87

Posted on:

Wednesday 24th October 2012
View Replies!