Then I need help getting on the right track here. What I really want to do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID
At 02:56 PM 9/28/2004, you wrote:
Then I need help getting on the right track here. What I really want to do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
You need to match up
- Original Message -
From: Eve Atley [EMAIL PROTECTED]
To: Paul DuBois [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 3:56 PM
Subject: RE: Multiple Insert Statement?
Then I need help getting on the right track here. What I really want to do
Hello,
I need to backup a mysql database in such a way that the output is
simply a bunch of insert statements. I do not want the database
schema as output as well: just the insert statements. This is
because I already have a script with CREATE statements and
would like to rebuild the database
: Multiple Insert Statement?
- Original Message -
From: Eve Atley [EMAIL PROTECTED]
To: Paul DuBois [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 3:56 PM
Subject: RE: Multiple Insert Statement?
Then I need help getting on the right
At 19:47 -0230 9/28/04, Neil Zanella wrote:
Hello,
I need to backup a mysql database in such a way that the output is
simply a bunch of insert statements. I do not want the database
schema as output as well: just the insert statements. This is
because I already have a script with CREATE statements
Hi!
I have a huge problem with the following insert statement:
INSERT INTO cds_catalog
SELECT cds_stage.cds_catalog.*
FROM cds.cds_catalog, cds_stage.cds_catalog
WHERE cds_stage.cds_catalog.prodidcds.cds_catalog.prodid;
Whenever I'm running it, it produces the error
Sebastian Geib wrote:
Hi!
I have a huge problem with the following insert statement:
INSERT INTO cds_catalog
SELECT cds_stage.cds_catalog.*
FROM cds.cds_catalog, cds_stage.cds_catalog
WHERE cds_stage.cds_catalog.prodidcds.cds_catalog.prodid;
Whenever I'm running
Say, for example, I want to run an insert like the following:
java.sql.Statement select = conn.createStatement();
select.executeUpdate(update test set observerNote='\u201C ... \u00BC');
FWIW, u201C is an opening curly quote and u00BC is a fraction representing
one quarter.
If I create my JDBC
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
This one has to be easy, but it seems to be hidden in the documentation
(translation: I'm too dense to find it g).
When doing an insert, there is the feedback line 1 Row Inserted. When
reloading a database from a backup, this can drastically slow
Hi,
I can vaguely remember there was something like INSERT... on duplicate
key UPDATE... in MySQL, but the documentation search is almost as
useful as I'm used to - it cannot tell me anything about this. Can you
please? How does this work, what's the syntax? Is this ANSI-SQL standard
I think what you mean is REPLACE. Try
http://dev.mysql.com/doc/mysql/en/REPLACE.html
Yves Goergen wrote:
Hi,
I can vaguely remember there was something like INSERT... on
duplicate key UPDATE... in MySQL, but the documentation search is
almost as useful as I'm used to - it cannot tell me
Yves Goergen wrote:
Hi,
I can vaguely remember there was something like INSERT... on
duplicate key UPDATE... in MySQL, but the documentation search is
almost as useful as I'm used to - it cannot tell me anything about
this. Can you please? How does this work, what's the syntax? Is this
ANSI
On Tue, 14 Sep 2004, Yves Goergen wrote:
Hi,
I can vaguely remember there was something like INSERT... on duplicate
key UPDATE... in MySQL, but the documentation search is almost as
useful as I'm used to - it cannot tell me anything about this. Can you
please? How does this work, what's
be created
with initial values. But there's a comment on that page that tells that
INSERT ... ON DUPLICATE KEY UPDATE is new in mysql 4.1.0. Hm, too
bad. OK, I'll have to work with 2 queries then, SELECT and then INSERT
or UPDATE.
--
Yves Goergen [EMAIL PROTECTED]
BlackBoard Internet Newsboard System
On 14.09.2004 22:44 (+0200), Tobias Asplund wrote:
It's covered on the INSERT page:
http://dev.mysql.com/doc/mysql/en/INSERT.html
If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and
(...)
Mmh, yes, thank you. There's just too much text. Anyway, v4.1 is too
late... I need
Mikhail Entaltsev wrote:
You _could_ try adding an identical primary key to the stat_in table as
you have on the 321st_stat table. However, since we need all of the rows
from stat_in in the results, I am not sure that it will help speed up the
join (because it's a left join). Even though I think
hi...
trying to figure out how to structure an insert/select for a multiple table
situation...
sort of...
insert table1, table2 (table1.item1, table1.item2, table2.item1,...)
select
a.q1, b.q2
from a1
left join a2
on a2.t=a1.t
where a2.r='4';
i can't seem to figure out the syntax
bruce wrote:
hi...
trying to figure out how to structure an insert/select for a multiple table
situation...
sort of...
insert table1, table2 (table1.item1, table1.item2, table2.item1,...)
select
a.q1, b.q2
from a1
left join a2
on a2.t=a1.t
where a2.r='4';
i can't seem to figure out
michael...
it was meant as an example, to convey what i want to do, which is do a
simltaneaous insert into multiple tables at the same time. the syntax
concerning the left join/elements to be inserted was not intended to be
syntacticly (sp?) correct!!!
and as i stated, searching through mysql
At 10:52 -0700 9/1/04, bruce wrote:
michael...
it was meant as an example, to convey what i want to do, which is do a
simltaneaous insert into multiple tables at the same time. the syntax
concerning the left join/elements to be inserted was not intended to be
syntacticly (sp?) correct!!!
and as i
Bruce,
Sorry, I guess I wasn't clear. I understood what you were asking, and I
thought I answered it. Your search of the mysql docs and google found
nothing about multiple-table inserts because you can't do that. I think the
mysql manual page I referenced is clear:
INSERT Syntax
INSERT
The question:
Is there anything in MySQL that will allow me to determine, accurately, the
last auto-incremented field from a particular database, so I can then insert
based upon this into another table? What if 2 users input at the same time?
Please see the 'long explanation' for further details
'
INSERT INTO resume (Candidate_ID, Section_ID, Section_Value)
VALUES (@candidateID, 1, '$field1'),
(@candidateID, 2, '$field2'),
(@candidateID, 3, '$field3'),
(@candidateID, 4, '$field4'),
(@candidateID, 5, '$field5'),
(@candidateID, 6, '$field6');
If you use this format, you have to worry about your
that will allow me to determine, accurately, the
last auto-incremented field from a particular database, so I can then insert
based upon this into another table? What if 2 users input at the same time?
Please see the 'long explanation' for further details
I never got a reply for this, and I'm still trying to figure out the
best way to handle it. Anyone?
John A. McCaskey
-Original Message-
From: John McCaskey [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 25, 2004 2:17 PM
To: [EMAIL PROTECTED]
Subject: INSERT IGNORE like feature
Mikhail Entaltsev wrote:
Hi,
insert into 321st_stat select * from stat_in group by primary key fields
from 321st_stat table;
did you try to use this query?
Best regards,
Mikhail.
Ran it, it took at least 24 hours, it finished but never gave me the
total time, when I checked the server mysql
Could you execute show create table 321st_stat and show create table
stat_in
and send results back?
Best regards, Mikhail.
- Original Message -
From: matt ryan [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Friday, August 27, 2004 4:45 PM
Subject: Re: 1 day 28 min insert
Mikhail
Mikhail Entaltsev wrote:
Could you execute show create table 321st_stat and show create table
stat_in
and send results back?
I have no key's on the temp table, stat_in, do you think adding keys on
the whole primary key would be faster?
I wasnt sure if you could join mysql keys, the key is
..
Have you considered wrapping your insert with :
ALTER TABLE `321st_stat` DISABLE KEYS
ALTER TABLE `321st_stat` ENABLE KEYS
?
matt ryan [EMAIL PROTECTED] wrote on 08/27/2004 03:25:58 PM:
Mikhail Entaltsev wrote:
Could you execute show create table 321st_stat and show create
: [EMAIL PROTECTED]
Sent: Friday, August 27, 2004 9:25 PM
Subject: Re: 1 day 28 min insert
Mikhail Entaltsev wrote:
Could you execute show create table 321st_stat and show create table
stat_in
and send results back?
I have no key's on the temp table, stat_in, do you think adding keys
be
ignored, it is worth a try to see if it would make a difference with a new
EXPLAIN..
But it should speed up grouping by PRIMARY KEY in:
insert into 321st_stat select * from stat_in group by
dic,niin,fr_ric,don,suf,dte_txn,sta;
Mikhail.
--
MySQL General Mailing List
For list archives: http
of performance gain for his original INSERT problem. It took just
over 24 hours to do an INSERT IGNORE to add just about 1000 rows to a 77
million row table. What metric of that performance will your GROUP BY test
help to prove?
Respecfully puzzled,
Shawn Green
Database Administrator
Unimin
be fine. :)
But I agree that it is MySQL-specific query.
I do not understand why you believe that a GROUP BY test will prove any
sort of performance gain
for his original INSERT problem. It took just over 24 hours to do an
INSERT IGNORE
to add just about 1000 rows to a 77 million row table
I have a logging table where I insert a large number of rows every 5
minutes. For performance reasons this occurs in bulk inserts of about
5000 rows at a time. (ie. INSERT INTO table VALUES(...), (...), (...))
One of the fields in the table is an id that connects it to another
table
Should mysql version 4.0.20 support the Insert data from a textfile into table which
is shown at the bottom of the page while in phpmyadmin with a database and table
selected? I am getting this error:
#1148 - The used command is not allowed with this MySQL version.
Thanks for any help,
Doug
matt ryan wrote:
The table is 9 gig, and the index is 8 gig
unfortunately the primary unique key is almost every column, if I were
to make it one using concat, it would be huge.
I tried making those fields a hash, but it did not work, I had
duplicate hashes for non duplicate records!!
Matt
If
Message-
From: Remigiusz Sokoowski [mailto:[EMAIL PROTECTED]
Sent: Friday, August 20, 2004 1:12 AM
To: matt ryan; [EMAIL PROTECTED]
Subject: Re: 1 day 28 min insert
matt ryan wrote:
The table is 9 gig, and the index is 8 gig
unfortunately the primary unique key is almost every
Hi,
insert into 321st_stat select * from stat_in group by primary key fields
from 321st_stat table;
did you try to use this query?
Best regards,
Mikhail.
- Original Message -
From: matt ryan [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, August 19, 2004 6:06 PM
Subject: 1
17.60 sec)
Rows matched: 77269086 Changed: 77269086 Warnings: 0
mysql insert ignore into 321st_stat select * from stat_in;
Query OK, 563 rows affected (1 day 28 min 35.95 sec)
Records: 77269086 Duplicates: 77268523 Warnings: 0
I just cant deal with speeds this slow, an insert onto a table
I'm guessing that you have indexes on the 321st_stat table? If this is the
case, try dropping them before you do the insert, then rebuilding them.
MySQL is known to be slow at doing bulk inserts on indexed tables. Also,
updates are much faster than inserts since with inserts there are much more
ctasc='321ST';
Query OK, 77269086 rows affected (24 min 17.60 sec)
Rows matched: 77269086 Changed: 77269086 Warnings: 0
This is very fast (53000 updates per second). If you are truncating
this table after the following insert, you can skip this step
completely by selecting field1,field2,'321ST
One alternative, since you know you don't have many records to insert,
is to pull the IDs of the missing records and insert just those. Do an
outer join on the two tables (joining on the primary key), get a list
of the IDs of records in stat_in but not in 321st_stat, and add a
WHERE id IN (list
- Original Message -
From: matt ryan [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, August 19, 2004 12:06 PM
Subject: 1 day 28 min insert
I think oracle parallel query is calling me
110,832,565 stat records
77,269,086 on weekly update, I get small daily files, but daily
Andrew Pattison wrote:
I'm guessing that you have indexes on the 321st_stat table? If this is
the case, try dropping them before you do the insert, then rebuilding
them. MySQL is known to be slow at doing bulk inserts on indexed
tables. Also, updates are much faster than inserts since
In the last episode (Aug 19), matt ryan said:
One alternative, since you know you don't have many records to insert,
is to pull the IDs of the missing records and insert just those. Do an
outer join on the two tables (joining on the primary key), get a list
of the IDs of records in stat_in
on
its keyfield field) but I should take much less time than your 24 minute
insert. Now add a key to tmpInsertMe to speed up the JOIN during the
insert. On 1000+ records (based on your example statistics) this should
take way under a second.
ALTER TABLE tmpInsertMe add Key(keyfield);
Then you
You are DEFINITELY not going to win any races with INSERT REPLACE.
Minimizing the number of records you need to process will improve your
times significantly, especially since you need less than .002% of your
source data added to your destination table.
Shawn Green
Database Administrator
that you create and destroy as part of the
process.
Are you using MyIsam or InnoDB? If MyIsam, then if you are scheduling the
insert as delayed and the table is still being read from, you may be
experiencing an issue where there are enough reads to keep the insert from
getting started.
Also
The table is 9 gig, and the index is 8 gig
unfortunately the primary unique key is almost every column, if I were
to make it one using concat, it would be huge.
I tried making those fields a hash, but it did not work, I had duplicate
hashes for non duplicate records!!
Matt
--
MySQL General
Hello All,
I have the following environment:
MySQL 4.1.3-beta-standard
Connector/J 3.0.14
SUN Java JDK 1.4.2
SUSE Linux 9.1
I have written a java program to insert information into an emp table
as follows:
INSERT INTO dirxml.emp
(fld_ind_id,fld_frname,fld_srname,fld_init,fld_sh_name1
Can you system.out.println the sql statement to verify what you have is
correct? Also, are you using prepared statements?
-Original Message-
From: Shaffin Bhanji
To: [EMAIL PROTECTED]
Sent: 8/11/04 2:07 PM
Subject: HELP!!! SEVERE: VendorError: 1064 during INSERT
Hello All,
I have
The statement is from a System.out.println() and my code is as follows:
public boolean insert(String sql) {
try {
System.out.println(Executing INSERT SQL Statement -
+ sql);
statement = conn.createStatement
Subject: RE: HELP!!! SEVERE: VendorError: 1064 during INSERT
The statement is from a System.out.println() and my code is as follows:
public boolean insert(String sql) {
try {
System.out.println(Executing INSERT SQL Statement -
+ sql);
statement
I didn't actually count the characters but it looks very close to a
512-character limit. (I used a pica stick on the screen, very accurate!)
The error message appears to be showing a truncated line. Is possible?
--Jon
INSERT INTO dirxml.emp
(fld_ind_id,fld_frname,fld_srname,fld_init
a major roadblock when trying to import
the biggest table (20+GB, with 4 indexes).
We have reproduced the problem with a simpler table on many different
servers and MySQL versions (4.X).
At first, we easily insert 1600+ lines per second. As the number of
lines grows, the performance deteriorate
any change,
but I don't expect it.
Luc
-Original Message-
From: Luc Charland [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 7:54 PM
To: [EMAIL PROTECTED]
Subject: Insert problems with InnoDB (big table)
We are evaluating the replacement of a Sybase database with MySQL
, this
isn't the biggest table that anyone has imported in innodb?
Dr. Frank Ullrich wrote:
Luc,
do you use the mysql client for the insert operations?
And is autocommit set to yes?
Then the answer is:
turn off autocommit mode and commit every high number but not too
high to grow InnoDB's transaction
[EMAIL PROTECTED] wrote:
Estimado Luc,
Con fecha jueves 5 de agosto de 2004, 11.07.23, escribió:
Did you try disable index table? When you import millon of records
there is an overload indexing it. First import and then create your
index or:
ALTER TABLE tb_name DISABLE KEYS;
import data...
to import
the biggest table (20+GB, with 4 indexes).
We have reproduced the problem with a simpler table on many different
servers and MySQL versions (4.X).
At first, we easily insert 1600+ lines per second. As the number of
lines grows, the performance deteriorate (which I can understand
Luc,
do you use the mysql client for the insert operations?
And is autocommit set to yes?
Then the answer is:
turn off autocommit mode and commit every high number but not too high
to grow InnoDB's transaction handling resources too big rows.
Commit every 100,000 rows for example.
The speeds up
Are you disabling autocommit before doing the inserts? And committing
after all inserts are complete?
-Original Message-
From: Luc Charland [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 7:54 PM
To: [EMAIL PROTECTED]
Subject: Insert problems with InnoDB (big table)
We
?
-Original Message-
From: Luc Charland [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 7:54 PM
To: [EMAIL PROTECTED]
Subject: Insert problems with InnoDB (big table)
We are evaluating the replacement of a Sybase database with MySQL. The
databases are 60+GB, containing more than 100
';
According to SHOW TABLE STATUS, it has approximately 200 million records and takes 25
GB.
One block of measurements typically consists of 256 records with the same values in
db_test_id and measurement_no. It takes 200 - 300 ms to insert. At first, I was
inserting the records one by one
, with 4 indexes).
We have reproduced the problem with a simpler table on many different
servers and MySQL versions (4.X).
At first, we easily insert 1600+ lines per second. As the number of
lines grows, the performance deteriorate (which I can understand), but
it eventually gets so slow
In article [EMAIL PROTECTED],
Adaikalavan Ramasamy [EMAIL PROTECTED] writes:
This seems more like the solution I want. I am using perl-DBI and when
there is an error (i.e. duplicate insert), the rest of the scrip it not
executed. But this is gives me the following error. What am I doing
wrong
firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname
). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb
), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
+--+---+--+
| myID
Thanks Alec ! This works wonderfully.
But I have another related question. How do I write an IF ELSE command
with MYSQL. In this context, I want it to return myID if the record
already exists, otherwise insert into database.
This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE
from http://dev.mysql.com/doc/mysql/en/INSERT.html:
14.1.4 INSERT Syntax
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
...
If you specify the ON DUPLICATE KEY UPDATE
This seems more like the solution I want. I am using perl-DBI and when
there is an error (i.e. duplicate insert), the rest of the scrip it not
executed. But this is gives me the following error. What am I doing
wrong ?
mysql desc tb
Adaikalavan Ramasamy wrote:
This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');
Assuming you have the unique index on (firstname, lastname), just do
INSERT IGNORE INTO tb
' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');
Assuming you have the unique index on (firstname, lastname), just do
INSERT IGNORE INTO tb (first_name, lastname) VALUES ('Jack', 'Doe');
But how are you planning to handle multiple people named Jack
Adaikalavan Ramasamy [EMAIL PROTECTED] wrote on 26/07/2004
16:31:44:
But I have another related question. How do I write an IF ELSE command
with MYSQL. In this context, I want it to return myID if the record
already exists, otherwise insert into database.
This naive syntax does not work
On Mon, 26 Jul 2004 17:47:37 +0100, Adaikalavan Ramasamy
[EMAIL PROTECTED] wrote:
This seems more like the solution I want. I am using perl-DBI and when
there is an error (i.e. duplicate insert), the rest of the scrip it not
executed. But this is gives me the following error. What am I doing
Note: I installed MySQL on my labtop (a Windows XP machine) which is
located at the same site as the Oracle server, just to compare the insert
times with the remote MySQL database. Inserts which were taking over 4
minutes were now completing in 90 seconds or less. Approximately 1 second
per
Scott,
The bottom line is that LAST_INSERT_ID() isn't guaranteed to be unchanged by
an INSERT IGNORE that doesn't insert anything, so you have to do something
else.
You need to test that a row was, in fact, inserted by the first INSERT
IGNORE and, if not, do not execute the second INSERT
on 7/22/04 10:56 PM, Scott Haneda at [EMAIL PROTECTED] wrote:
I am not sure this is just how it is, my issue, or a bug, I searched the
bugs database, but don't really know what to call this in order to properly
look up the info:
MySQL 4.0.18-standard
Also note, I just tested this on
on addresses.email_address and
addresses.user_id, note the unique key on address_id in the second table.
I insert the following data TWICE:
INSERT IGNORE INTO `addresses`
(`user_id`,`first_name`,`last_name`,`email_address`) VALUES (1, 'Claire',
'Altman', '[EMAIL PROTECTED]');
INSERT IGNORE
on 7/23/04 5:30 AM, Harald Fuchs at [EMAIL PROTECTED] wrote:
The behavior is indeed strange, but it's not a bug, since it's documented:
If you use `INSERT IGNORE' and the record is ignored, the
`AUTO_INCREMENT' counter still is incremented and
`LAST_INSERT_ID()' returns
I have been pulling my hair out trying to get a solution to something,
assuming idiotically that in a transaction scenario I would not be able to
get the insert it back out. It seems to work, I am wondering how and if it
is reliable.
Give the scenario where I have 2 inserts I want to make, since
MySQL doesn't guarantee that there will be no gaps in sequence values.
Assigment of the id is always atomic because innodb uses an AUTO_INC
lock that lasts for the time of the insert, not the life of the
transaction.
lets say your highest order number is 10
transaction begins for client 1
on 7/22/04 3:54 PM, Justin Swanhart at [EMAIL PROTECTED] wrote:
MySQL doesn't guarantee that there will be no gaps in sequence values.
Assigment of the id is always atomic because innodb uses an AUTO_INC
lock that lasts for the time of the insert, not the life of the
transaction.
lets say
insert the following data TWICE:
INSERT IGNORE INTO `addresses`
(`user_id`,`first_name`,`last_name`,`email_address`) VALUES (1, 'Claire',
'Altman', '[EMAIL PROTECTED]');
INSERT IGNORE INTO `addresses_incampaign` (`user_id`, `address_id`,
`campaign_id`) VALUES (1,LAST_INSERT_ID(), 2);
Notice I am
I?ve developed an Oracle PL/SQL stored procedure that takes information
from an Oracle 9.2.0.5 database and inserts this information into a MySQL
4.0.17 MyISAM table. The insert takes over 3 minutes to insert
approximately 90 records based on an Oracle SQL Trace. A few things that
may
Hi,
Using the following query I am attampting to insert the Work_Type_IDs from
Work_Types WHERE Project_ID = 'x'. However I also wan to insert the
Project_ID into the table, how would this syntax work - apperently the
column count is incorrect...
INSERT INTO Letter_Templates (Work_Type_ID
Shaun,
You need two columns for the insert, but you're only selecting one.
Try this:
INSERT INTO Letter_Templates (Work_Type_ID, Project_ID) SELECT
Work_Types.Work_Type_ID, Work_Types.Project_ID FROM Work_Types WHERE
Work_Types.Project_ID = 'x';
Andy
-Original Message-
From: shaun
Is there a way to do an INSERT on a table only if no row already exists with
the same info for one or more of the columns as the row to be inserted?
That is, without using a method outside SQL?
Thanks,
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
Certainly, it's called making a unique index on the field(s) you want to keep
unique.
Hope that helps.
j- k-
On Wednesday 07 July 2004 12:48 pm, John Mistler said something like:
Is there a way to do an INSERT on a table only if no row already exists
with the same info for one
Create a unique index on each column that you don't
want to be duplicated.
create UNIQUE index table_u1 on table(some_column)
--- John Mistler [EMAIL PROTECTED] wrote:
Is there a way to do an INSERT on a table only if no
row already exists with
the same info for one or more of the columns
This is certainly the first step.
Also, if you want to insert only those rows which are not already present,
you can use something akin to:
INSERT INTO table1
SELECT * FROM table2 t2
LEFT JOIN table1 t1 ON (unique row identifiers -- whatever these are for
your data)
WHERE t1.XYZ IS NULL
Try insert ignore. Check the manual page for insert syntax.
On Wednesday, July 7, 2004, at 05:22 PM, Matt Chatterley wrote:
This is certainly the first step.
Also, if you want to insert only those rows which are not already
present,
you can use something akin to:
INSERT INTO table1
SELECT
like:
Is there a way to do an INSERT on a table only if no row already exists
with the same info for one or more of the columns as the row to be
inserted? That is, without using a method outside SQL?
Thanks,
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com
where you are trying to find instances of one OR the other.
If you are doing a number of inserts or multi-row inserts you may want to
use the IGNORE parameter. This will allow the INSERT command to complete
and return successfully, simply skipping the rows where dupes are found,
otherwise mysql
John,
Here is an example which should clarify for you:
INSERT INTO testTable (a,b) VALUES ('AVAL', 'BVAL');
Let's say testTable looks like this:
+-+
| a | b |
--|
| AVAL | EVAL |
| FVAL | BVAL |
+-+
You have your unique index over (a,b). The insert
like:
Is there a way to do an INSERT on a table only if no row already exists
with the same info for one or more of the columns as the row to be
inserted? That is, without using a method outside SQL?
Thanks,
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
something like:
Is there a way to do an INSERT on a table only
if no row already exists
with the same info for one or more of the
columns as the row to be
inserted? That is, without using a method
outside SQL?
Thanks,
John
--
MySQL General Mailing List
For list archives: http
making a unique index on
the field(s) you
want to keep
unique.
Hope that helps.
j- k-
On Wednesday 07 July 2004 12:48 pm, John Mistler
said something like:
Is there a way to do an INSERT on a table only
if no row already exists
with the same info for one or more of the
columns as the row
Description:
On a table with a auto_increment/primary constraint is it not possible to
insert an id with value 0 but it is possible to insert it with another
id and use update to set it to 0, thereby making the backup import later
fail due to the use of insert statements.
How-To-Repeat:
mysql
901 - 1000 of 2077 matches
Mail list logo
