MYSQL :: Insert Data Into 2 Tables With 1 Query Fails

Insert Fails On Access
I have a database with about ten tables. I'm trying to fill one table with
some dummy data (its a contact manager table holding names of people,
addresses and whatever but I'm createing random values for the moment). The
insert fails telling me that access is denied - however I use the exact same
username/password/database name/host name on the other tables, and the
insert and updates work just fine.

I know this sounds odd - but is there any other reason as to why I might get
the access denied message, other than what I assume should be just for an
invalid username/password? Code:

How To Insert Data Into 2 Tables At Once
I am realizing now that I have never done this before. I need to insert information - all at once into two seperate tables joined by a fk.

So essentially I need to insert into one table then get the primary id from it and then insert this value long with the other fields - into the 2nd table.

Can I do this without writing 2 insert statements - perhaps using a join?

If so what is the basic syntax?

Insert Data Into 2 Tables Using Php
i was wondering how i would go about insert data collected on a php form into 2 separate tables. i want to collect email and password for an admin table and everything else for a separate table why cant i just do this?

$query = "INSERT INTO user_admin (s_email, s_password, salt) VALUES ('$email', '$encrypted', '$salt')";
mysql_query ($query) or die ('Could not register new account.');

$query = "INSERT INTO student (s_forname, s_surname, s_uni_id, s_email, s_course,) VALUES ('$forename', '$surname', '$uni_id', '$email', '$course')";
mysql_query ($query) or die ('Could not register new account.2');

the email and password get stored into the user_admin table but nothing is collected for teh student table do the values such as $forename need to be declared anywhere else before?

Insert Multiple Fails Completely If Any Duplicates Exist
I've been having some problems with database performance... Several
threads are constantly attempting INSERTs of new records into a large
table - that is hundreds of thousands of records -large by my account
:-) The table has a VARCHAR field tagged as UNIQUE and inserts are
kind of slow. i'm relying on the INSERT to fail on duplicates, i'm
not performing any checking before i attempt the INSERT (i figured an
additional SELECT would slow things even more - but any advice to the
contrary would be appreciated).

While these back end threads are constantly banging away with their
INSERTS, front end users are hitting the DB with searches. so i end
up with several threads simultaneously attempting very expensive DB
operations. the result is that the front end that users see is
DREADFULLY slow.

My proposed speedup is to modify the behavior of the backend threads
so that they don't constantly attempt INSERTS of single entries, but
build up a hashtable of local entries and at some interval, attempt a
multiple insert something like:

INSERT INTO table (col1,col2,col3 VALUES (x1,y1,z1), (x2,y2,z2)....

While i do expect to take a big hit when these multiple inserts
launch, in the time between, i expect front end performance to be
significantly improved.

The problem with this situation is that the multiple insert may
contain one or more new entries which will cause duplicates on my
table's UNIQUE VARCHAR field. I would like to have duplicates ignored
on insert, but have all newly unique entries be added. Sadly, from
what i've seen, once a duplicate violation for any of the new entries
is found, the call fails, leaving my newly UNIQUE entries NOT ADDED.
Is there any way to get the call to work as i want? Doing many single
row inserts sounds like it'll be much slower (judging from what i've
read) so i'd rather not resort to that.

I don't have a ton of DB experience (which may be obvious) so if i've
overlooked something simple please let me know. If there's a better
way to improve performance other than these multiple inserts, that
would be nice to know about too. Thanks for any insight,suggestions,
etc...

Insert Data Into Multiple Tables
Can I insert data into multiple tables at once? Basically just need to insert a number into the 'membersID' column into 10 tables, but is taking ages, so wondering if I can write a query which targets all the tables or is this impossible in SQL?

Any Way To Insert Data Into Two Tables At The Same Time?
what I have is a table called `order` and a table called `lmcharge`.
a person inputs data from a form and i need to take the data from both and some goes into one table, some goes into another
here is an example:

order values:
student_id, class_id, order_type, date, status
//student_id, class_id, order_type will be from the form

lmcharge values:
student_id, order_id, lm_number
//student_id, and lm_number will also be from the form but order_id will have to be from the order table

anyone have a clue?

Way To Insert Data Into Two Different Tables In Mysql Thru Jsp
I'm working on a project which needs data from existing tables to be displayed as history tables.So i want to know whether there is anyway to insert data into two different tables in mysql thru a single query in a jsp code.

Insert Data Into Related Tables
I'm sure that this question has been asked many a times but I can't find it, probably don't know the right keywords to find it.

I am new to MySQL, working knowledge of MS Access, so quite a leap forward. I have 2 tables (for learning purposes)

1. pages
page_id
page_title
...

2. articles
article_id
page_id
article_title
...

If a create a new article_id for which a page_id does not already exist, what method must I use. Basically how can I create a new article and page entry simultaneuosly? Must I do 2 seperate inserts (is this when a transaction should be created) or one using some sort of join? How do I ensure that whatever auto-increment page_id number is created by the database is associated to my new article entry?

Insert Data Into Multiple Tables
Is it possible to insert data into multiple tables with one insert statement?

LOAD DATA Fails When Non-ascii Chars In Data
I am trying to load a cities database that is in CSV format. Some of the fields contain extended characters like the umlaut or accented chars. The load fails with: Code:

Insert Into 2 Tables In 1 Query
how do i insert into 2 tables in 1 query?

Create A Query To Insert Data
I have a form that includes a bunch of fields to fill out. I have the query working for most of them, but right now am stuck on getting one area to work.

I have 7 fields where you can input a parts number (name=number[]) and then another 7 fields where you input the description of the part (name=description[]).

I want to put them into a database that has one column for the number, one column for the description and then a primary key and foreign key to eventually join them to the main workorder table.

I am not sure how to make a query that will INSERT them into the DB columns, one row for each number/description combination, and check to make sure there is data in the rows. There may not always be 7 part number/descriptions in the form fields and I don't want a bunch of empty rows in the DB table.

Insering Data Into Bit Field Fails
I know there's an issue with mySQL not being able to display BIT values, but that's not the issue, it's just not inserting them.

Here's some info about server / table before i explain the problem.

mysql Ver 14.12 Distrib 5.0.24a, for pc-linux-gnu (i486) using readline 5.1

The field causing problems is bit(3)

Right, when I do...

INSERT INTO waypoints (lat_long,date,type,marker) VALUES('12313123132,1231321','1174415400','2','1');

I get the line inserted and I get a warning, as follows.

"Out of range value adjusted for column 'type' at row 1"

At first I assumed this was because I was entering a decimal number, but when I do an update it inserts the decimal number correctly.

Query Fails In MySQL 5
I just migrated a Mambo CMS site from a server runing MySQL 4 something to one running MySQL 5.0 and am getting one problem (was expecting many more) with a query generated in the admin section:

SELECT c . * , g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name
FROM mos_content AS c, mos_categories AS cc, mos_sections AS s
LEFT JOIN mos_users AS u ON u.id = c.checked_out
LEFT JOIN mos_groups AS g ON g.id = c.access
LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id
WHERE c.state >=0
AND c.catid = cc.id
AND cc.section = s.id
AND s.scope = 'content'
AND c.sectionid = ཊ'
ORDER BY cc.ordering, cc.title, c.ordering
LIMIT 0 , 10

MySQL said: Documentation
#1054 - Unknown column 'c.checked_out' in 'on clause'
The problem appears to be with the fact that the table mos_content is aliased as c and the the SELECT uses a wilcard to select all its columns. For some reason, when the ON clause test the value in any of the colums using the alias, it says they are onknown.

Using IF() For Choosing The Query Fails
The following code is for the Archive. (Where users can store special messages)
I want to receive one row for each message that has been sent OR that has been received.
With the code beneath I get this error: "#1241 - Operand should contain 1 column(s)". How can I fix this?

INFO AND CODE ......

UPDATE Fails On Somewhat Large Data Sets
I am running into a problem with an UPDATE statement. I am using MySQL 5.0 server, and the mysql command line client that came with it.

update tableA a, tableB b set a.value = b.value where a.key1 = 'foo' and a.id =
b.id and a.col2 = b.col2;

The tables use InnoDB as the engine. With small data sets, this works fine. However, with larger data sets, the UPDATE runs for a long time, then I get an error saying that the number of locks exceeds lock table size. In my case, the data sets in the two tables are < 5 million rows. I kept monitoring the InnoDB status, and see the number of log entries go up until finally rolling back.

I saw a bug on the mysql bugsite: http://bugs.mysql.com/bug.php?id=15667 which sounds like the problem I am facing - though my data sets are not as large as the bug states.

How do I overcome this issue? I was thinking about splitting the query into smaller ones, using LIMIT and doing some sort of ordering to guarantee same order of rows retrieved.

Any better ideas? Maybe configuring the InnoDB differently (I read that the lock table size cannot be changed, darn.)

Cross Table Query Fails
I get this error which I can't quite understand....PHP Code:

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 'select x10_channel from devices where device = 'Lamp')' at line

The line mentioned involves a cross table query:PHP Code:

$sql = "select commandID from commands where x10_channel = (select x10_channel from devices where device = '$device')";

which works perfectly on a friends laptop. I'm guessing there is a configuration difference between our installations.

Get 4 Tables Data In One Query
I am new to mysql relational programming, and creating a tutorial site like pixel2life. I am trying to get the data from 4 tables 1st table is main tutorials table, 2nd is subcategory (subcat) table, 3rd is categories table and 4th is users table. I don't know how to do it exactly, I googled but not got the proper method. I only got some hints http://www.brainbell.com/tutorials/M...ing_A_Join.htm from this site. I've created the following query which selects the title from tutorials table, subcategory (s_cat) from subcat table which reference is available as s_cat_id in tutorials table, then category (cat) from categories table which reference is available in subcategory (subcat) table and username from users table which reference is availble in tutorials table. Can somebody tell me this query is correct or there is any alternate method

PHP

SELECT d.title, s.s_cat, c.cat, u.username FROM tutorials d
JOIN subcat s ON d.s_cat_id=s.id
JOIN categories c ON s.cat_id=c.id
JOIN users u ON d.author_id=u.id

Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17):

"update user inner join events on user.username = events.username
set user.LastMessageDate = '$current_time'
where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'"

but fails on my production server running v3.23.56.

Anyone any ideas why it doesn't run and what I need to do to fix it?

Retrieve Data From 2 Tables In 1 Query
I've got 2 tables content & sections I need to:

SELECT id, title, section_id , status FROM content

and now I'd like to get title from sections WHERE id = section_id from the first part of the query.

I've tried with unions but doesn't seem to like it..

Determining Which Table To Query Based On Data Within Tables
I have 2 tables:

default_categories
column 1: category_id
column 2: category_name
column 3: category_parent

custom_categories
column 1: custom_cat_id
column 2: custom_cat_name
column 3: custom_cat_parent

The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table.

So if the default category has 3 rows with IDs | names:
123 | Dogs
456 | Cats
789 | Fish

And the custom category has 1 row with IDs | names:
456 | Very Cute Cats

I want my query of these 2 tables to produce the following IDs | names:
123 | Dogs
456 | Very Cute Cats
789 | Fish

I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id.

MySQL
SELECT *
FROM default_categories
LEFT JOIN custom_categories ON category_id = custom_cat_id
WHERE category_parent = ''
AND custom_cat_parent = ''
ORDER BY $order_by $sort

Merge Sort-of Similar Data From 2 Tables In Query
A bit of background. I'm not a web person, and I know enough about databases to scrape by. Most of my SQL is generated using templates or handy things (like PHPMyAdmin etc). I'm also not a PHP coder. I am a C/C++ coder so I've found I can hack some PHP together, and I've got roped into helping a friend with a problem

Here's the issue. I have a MySQL database with 2 tables of interest. Let's call these tables categorydata and itemdata. Both of these tables contain records that describe individual items - the item name, its ID in the system, the date it was added, some free text about it etc.

To be absolutely clear, the purpose of the categorydata table is for a record to define a category (think "puzzles", "board games", "vegetables", whatever), but the record row also includes data on the FIRST item in the category ("banana", "cluedo" etc) along with all its data. The purpose of the itemdata table is to list all the OTHER items in the category ("grapes", "twister" etc). Here's an example record in the categorydata table:

categoryname vegetables
categoryid 1234
categorycreated 10th Jan 2000
categoryowner Bob
firstitemid 4567
firstitemname cucumber
firstitemcreated 12th Jan 2000
firstitemdesc It's long and green
Here's an example record in the itemdata table:

itemid 4568
itemname orange
itemcreated 13th Jan 2000
itemdesc It's round and, er, yeah.

This seems really bad design to me but what do I know.

Here's what I need to do. I need to know how to merge the data in these 2 tables in a single query, ordered by the itemid and firstitemid fields.

In other words, I need the query to merge the data, ordering it so that regardless of the table the record came from the itemids are all in correct numerical order. I need to do this so that I can retrieve each item's data in order.

itemid in the itemsdata table and firstitemid in the categorydata table are unique, so when merged there won't be any colliding ids.

I need to put this in a PHP script (in a loop that dumps out all the items in order), but if someone can just help me with the SQL query syntax I'd appreciate it, I can do the rest.

I think this has something to do with inner joins but I really don't understand how to make it work, or how to retrieve and merge data from 2 tables at once bearing in mind the fields (columns? what do you call them) have different names even though they have the same purpose (ie: firstitemid -v- itemid, firstitemname -v- itemname etc).

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?

Insert Tables
I am using mysql through the contol panel on windows server. basically trying to build a dynamic website. In the control panel, i can see the menus to create DSN and database.

Can anyone tell me where do i create the tables there is no site builder available, so should i create externally and then upload through ftp.

Insert From 2 Tables
I want to insert into a table field1, field2, field3, from table_one, and I want to add onto the end of field3 from the same field-name in table_two.

Insert into table_one(field1, field2, field3)
Select field1, field2, field3
From table_one

â€¦ and then take field3 from table_two and add it onto the end of the text entered from field3 from table_one.

Is this possible.

INSERT Into Two Tables
How do I insert data into two tables at the same time?

My DB structure is as follows:
CREATE TABLE `speakers`(
`speaker_id` INT(9) NOT NULL auto_increment,
`speaker` VARCHAR(30) NOT NULL,
PRIMARY KEY (speaker_id);
)

CREATE TABLE `RSS_info`(
`cast_id` INT(9) NOT NULL auto_increment,
`speaker_id` INT(9) NOT NULL,
`title` VARCHAR(50) NOT NULL,
`filename` VARCHAR(20) NOT NULL,
`description` VARCHAR(200) NOT NULL,
`pubDate` VARCHAR(32) NOT NULL,
`size` INT(20) NOT NULL,
PRIMARY KEY (cast_id),
FOREIGN KEY (speaker_id) REFERENCES `speakers` (speaker_id) on delete cascade
); TYPE=MYISAM

My INSERT statement for one table is below.

INSERT INTO `speakers` VALUES (NULL, 'Speaker Name');

What I need to be able to do is insert the rest of the data that I'll be receiving into the second table.

2 Tables And An INSERT
im trying to copy a row from one table to the other, but 1 of the table's has an extra AUTO_INCREMENT column.

INSERT INTO fixtures_h values (NULL, SELECT * FROM fixtures WHERE No = 1).

Insert In 2 Tables
I have two tables message, contacts, sch_contacts

columns for table message: messageid,description
columns for table contacts: contactid,email
columns for table sch_contacts: messageid,contactid

Now, i have to insert one row from table message and all the rows from table contacts in to table sch_contacts.

insert into sch_contacts(1,(select contactid from contacts))
for ease 1 can be treated as messageid which user is going to input.
I am getting error on sql which says subquery return more than 1 row
how to insert all the rows from 1 table into another?

2 Tables INSERT
I have two tables, and both have an id field. the id field in the first table is auto_increment, so when i insert data, i leave this field out. the problem is, i need to insert the same id number into my other table, but i cannot use auto_increment in the id field in the second table, because there are more than one occurence of each number.

How To Insert Into Multiple Tables
I have a situation where I need to insert into 2 tables that are related through ID and V3DETAILKEY.

The tables are:
V3DETAILSERVICE (ID VARCHAR(9), V3DETAILKEY VARCHAR(20), SERVICE NUMBER(7,4))

V3DETAILSALARY (ID VARCHAR(9), V3DETAILKEY VARCHAR(20), SALARY NUMBER(9,2))

So, the ID is 000001, SERVICE is 12.00, SALARY is 2200.00, V3DETAILKEY is automatically generated. Now if I was just going to insert into each table separately this is how I would do it:

INSERT INTO V3DETAILSERVICE
VALUES ('000001', V3DETAILKEY_SEQ.NEXTVAL, 12.00)

INSERT INTO V3DETAILSALARY
VALUES ('000001', V3DETAILKEY_SEQ.NEXTVAL, 2200.00)

However I need to update both tables with the same V3DETAILKEY and I am not sure how to do this.

Insert Data
Can someone write me a simple, tested, program that displays a form asking for a name with a submit button. When the submit button is pressed the name is then entered into a mysql database?

Data Insert
I am using PHP + MySQL and buld websites. Recently I encountered one problem. Actually I am using CMS to insert data in MySQL, but it seems to insert only limited amount of it. For example, I have a large text, and when I try to insert it, it becomes cut. I am sure, id does not depend on CMS, but I cannot figure out, what should I change in MySQL settings or variables?

To sum up: I don't know, how to increase the amount of text I try to insert - thus it becomes cut.

Insert New Data
something happens on one table (not on others) i never observed before : when i insert a new data, it's written at the begining, not at the end of the table ?! could you tell me why ? this is a bug ?

Insert Data
if it is possible to make a table with parameters such as (name varchar(20), text varchar(2000)) and have it store somethng like this is text field:

'TUTORIAL
Step 4: Create a table in the database

mysql> create table Employee(employee_number int(10) primary key auto_increment not null, First_Name varchar(30) not null, Last_Name varchar(30) not null);
Query OK, 0 rows affected (0.01 sec)'

It gives me an error if I try an INSERT command. Is it possible to do this or do I have to store the text in a file and refer to it using my database?? Thanks.

How Do I Insert An Auto Increment ID Into Other Tables
I have been trying to insert An auto_increment ID of one table into other tables and I am getting wierd results. I am trying to insert the CLIENT table primary key into the other three tables and it is inserted correctly into the CONTACTS table but the JOBSHEET and KNOWLEDGE tables both insert 1 into the clientID field. Code:

Compare And Delete Or Insert Within Tables
I have a CSV file that contains addresses. I need to compare the csv file to a MySQL table (tbl_bl).

Then if the addresses are not found in tbl_bl, I need to add the addresses to another table (tbl_wl) Only if they do not already exist in the tbl_wl table. I can create simple SQL queries but I'm not so sure about something this complex.

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 Data From Xls To Mysql
I have huge data which is in Ms Excel files (*.xls). Data is categorized in columns and rows.

INSERT DATA INFILE
I have a csv file that im inserting data from into a table. The problem I have is that I want to be selective on which columns to insert into the table. Then on top of that, 4 of the columns will need to be formatted to DATE format as they are currenty in dd/mm/yyyy format. Here is the command i have right now:

LOAD DATA INFILE 'advexport.csv' INTO TABLE data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
' IGNORE 1 LINES

And here is a list of the columsn I need to grab from the CSV file and put into the table:

1-7,12,13,17-28

Im ignoreing line 1 because its just a header line.

Insert Randomly Data
i need to enter some integer numbers (randomly) into a filed... any idea?

Insert Using Some Data From Another Record
i have a table with four fields: (one, two, three, four) the first three fields make up a composite key. i want to insert multiple records that just changes the value for field "one". example:

current record:

(11, 22, 33, 44)
i want to add the record (22,22,33,44), then also add (33,22,33,44), also (44,22,33,44), etc.

i use the innoDB version. is there any easy sql that does this?

Insert Data Infile
I have a csv file that im inserting data from into a table. The problem I have is that I want to be selective on which columns to insert into the table. Then on top of that, 4 of the columns will need to be formatted to DATE format as they are currenty in dd/mm/yyyy format. Here is the command i have right now:

LOAD DATA INFILE 'advexport.csv' INTO TABLE data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
' IGNORE 1 LINES

And here is a list of the columsn I need to grab from the CSV file and put into the table:

1-7,12,13,17-28

Insert A Large Of Data
how to insert a large of data in .sql file format (include CREATE TABLE and INSERT TABLE), because after I export data to backup from my website, data file aprox 50MB, and script always timeout when I restore data using phpMyadmin?

My Server use Linux OS and I have root account. May I use command line? Please help me and guide me step by step

Insert Multiline Data
1.) I have a table, pms, and a colum, MSG, the MSG colum's type is Text. I need to be able to insert multiline data, like:
Hi
Blah
Blah

..And still be able to echo it in that format.
2.)How can I find a row in a table?

Insert Data Via A .sql File
I've just started using php and Mysql for the first time. I am following a couple of sample sites that are created in a book. I have created the database with the tables. I have a file called populate.sql which contains some sample data. The instructions in the book show that to use this file I can use Mysql with the following :

mysql -u root -p < populate.sql ;

Do I have to use root or can I use another user. I have tried it with another user but it doesnt work. I get a syntax error.In another example I have managed to use the LOAD DATA INFILE.... command. I would just like to get this method to work. I guess its something really daft but its doing my head in.

Insert Bulk Data
Hello, first post here, hope i'm not braking too many rules, I have a question so I'll get to it:

I have a file with the rows in this format:

XXYYYYYYYYYY xx xxxx

the XX is a fixed header, the YY is the info I want and the rest is garbage.

Can i Specify that i want the data from a specific point to a specific point? and not the whole line?If so, can i specify this when using LOAD DATA INFILE ?

INSERT Data And RETRIEVE
I realize this is a simple question. I tried to search through the forums but am not sure how to word the question to get any results. My search queries returned no matches from the forums...
If you can answer my question that would be great. Even better would be a suggestion on how to search this forum so that the answer turns up.

this is the SQL so far:

INSERT INTO image_object SET image_name='11185131276164.jpg', image_float='left', image_caption='Caption', image_alt='Alt Text';

What i need is for MySQL to return a result telling me the auto_increment id that was given to the record at the time of the insertion.

Insert Into Single / Select From Multiple Tables
I have two tables from two databases (joomla2.jos_content and maxdev.jos_content) that I need to pull data from in order to populate a single table (joomla2.jos_magazine)

Here is what I want to do (I know this query doesn't work but you get the idea what I am trying to do)

insert joomla2.jos_magazine_articles
(joomla2.jos_magazine_articles.name,
joomla2.jos_magazine_articles.article_id,
joomla2.jos_magazine_articles.category_id,
joomla2.jos_magazine_articles.catid)

select
joomla2.jos_content.title,
joomla2.jos_content.id,
joomla2.jos_content.catid,
maxdev.jos_content.catid