Dear All
I'm trying to insert a bunch of data from TableA in TableB by doing
SELECT INTO TableB (fieldA, fieldB, ...)
SELECT fieldA, fieldB, ... FROM TableA GROUP BY fieldA, fieldC, ...
ON DUPLICATE KEY UPDATE fieldZ = VALUES(fieldZ);
On my PC this works fine. But on the Server, not all rows get
: none
>Synopsis: spurious select ERROR 1191 when insert into .. select * is done
>on fulltext table
>Severity: serious
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-5.0.51a (MySQL Community Server (GPL))
>Server: /usr/local
I am tying to dupilicate a set of records changing only one field. That
field is part of a UNIQUE key. So far I copy the records into themselves
and keep the same number of records:
insert ignore into user_projects select * from user_projects where
user_id="[EMAIL PROTECTED]" and fac_id="FAC-
Subject: Re: insert into... select... duplicate key
Relevant bits of the conversation so far, with my thoughts at the end:
Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> Here is the problem that I am having. I am trying to make a copy of a
> full record in a table that has a p
Relevant bits of the conversation so far, with my thoughts at the end:
Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
Here is the problem that I am having. I am trying to make a copy of a
full record in a table that has a primary key with auto-increment. The
real problem is that I
I am not familiour with Cold Fusion but: cant you use 'show columns from
table' ?? and use the result object?
This normally works in e.g. C or PHP
danny
Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
I am using Cold Fusion and as I stated in my original message, if I were
using
I am using Cold Fusion and as I stated in my original message, if I were
using MySQL 5, then I could use information_schema to retrieve the
column names in the table and do it with variables in Cold Fusion. I do
that on all my pages on the MySQL 5 servers with which I work. However,
the server I am
Well I haven't realy found a select method in which you can isolate a
field. Like a complementary method, in which you select like one field,
but shows the fields except the field which you have used in your
select-statement.
So you excually want to dynamically insert the records, not knowing
That is the effect that I am looking for, but exactly the method that I
am trying to avoid. If I type the column names into my INSERT... SELECT
and someone later adds a column to the table, I would have to go back
into my program and update the statement. I am looking for a way to do
it dynamically
Hi,
I am hoping you meen this:
You have to use the fields in your into -statement and select statement,
not including the field having the auto-numbering
so if e.g. field1 has autonumbering ->
insert into table1 (field2, field3) select (field2, field3) from table1;
autonumbering will automat
Here is the problem that I am having. I am trying to make a copy of a
full record in a table that has a primary key with auto-increment. The
real problem is that I want the statement to use SELECT * so that if
columns ever get added to the table the statement will still work for
the full record. I
.my> cc:
Fax to:
05/27/2004 05:01 Subject: Re: slow insert into select
statement
Thanks for all the feedback. Here's my latest attempt:
SELECT @start:=NOW();
SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH);
SELECT @date2:=CURDATE();
LOCK TABLES rptPricingTEST READ, rptPricingTEST a READ;
CREATE TEMPORARY TABLE tmpLatestDates
SELECT
commodity,
MAX(PricingDt)
05/26/2004 10:28 Subject: RE: slow insert into select
lto:[EMAIL PROTECTED]
Sent: Wednesday 26 May 2004 14:13
To: 'nyem '; '[EMAIL PROTECTED] '
Subject: RE: slow insert into select statement
If you do the math a large result set will be created. You could
rewriting your query or adding more indexes to see if this speeds up the
p
If you do the math a large result set will be created. You could rewriting
your query or adding more indexes to see if this speeds up the process.
-Original Message-
From: nyem
To: [EMAIL PROTECTED]
Sent: 5/26/04 2:57 AM
Subject: slow insert into select statement
I have this insert
I have this insert statement that took a long time to execute:
INSERT INTO priceLast5
SELECT
DISTINCT a.commodity,
a.PricingDt
FROM rptPricingTEST a
WHERE
a.PricingDt = (
SELECT MAX(PricingDt)
FROM rptPricing
references another
> table)
>
>
>
> I get an erro saying that i cannot use that table in the select, the table
> is a InnoDB.
>
> Can i use the same table in the insert into select? do i have to use a
> temporary table?
>
What version do you use?
You can insert data
int
data_colocacaodate
cod_componenteint (FK references another
table)
I get an erro saying that i cannot use that table in the select, the table
is a InnoDB.
Can i use the same table in the insert into select? do i have to use a
tempo
OK, I found the answer.
CREATE TABLE ProductSums
SELECT DISTINCTROW IndenturedList.NSIPartNumber,
Sum(tblInvTransaction.Qty) AS SumOfQty
FROM IndenturedList;
Table ProductSums has not been created. Can you create this table when
you insert data into it?
INSERT INTO ProductSums
SELECT DISTINCTROW IndenturedList.NSIPartNumber,
Sum(tblInvTransaction.Qty) AS SumOfQty
FROM IndenturedList;
Riaan Oberholzer <[EMAIL PROTECTED]> wrote:
> Is there a limit on the number of rows that can be
> inserted in this way? Ie, at what point should I start
> worrying about "out of memory" errors or something
> similar?
>
> So, how many rows can/should be returned by the SELECT clause?
There is no
Hello Riaan,
RO> Is there a limit on the number of rows that can be
RO> inserted in this way? Ie, at what point should I start
RO> worrying about "out of memory" errors or something
RO> similar?
RO> So, how many rows can/should be returned by the SELECT clause?
I had a similar case.
Difference i
Is there a limit on the number of rows that can be
inserted in this way? Ie, at what point should I start
worrying about "out of memory" errors or something
similar?
So, how many rows can/should be returned by the SELECT clause?
__
Do you Yahoo!?
Yahoo! SiteBuilder
day, November 22, 2002 12:30 AM
Subject: Re: INSERT INTO () SELECT...
> Hi Eric,
>
>thats oracle function INSERT INTO ... SELECT. it doesn't work in
>mysql.
>
>Mirza
>[EMAIL PROTECTED]
>
> __
> 21.11.2002
Hi Eric,
thats oracle function INSERT INTO ... SELECT. it doesn't work in
mysql.
Mirza
[EMAIL PROTECTED]
__
21.11.2002 22:13
> Hi,
> This should work, I think, but doesn't
> INSERT INTO holds (ord_num)
> SELE
PROTECTED]
Subject: INSERT INTO () SELECT...
Hi,
This should work, I think, but doesn't
INSERT INTO holds (ord_num)
SELECT orders.ord_num FROM orders
LEFT JOIN holds ON orders.ord_num = holds.ord_num
WHERE holds.ord_num IS NULL
I have some order numbers that are not in holds that are in orde
At 11:15 -0600 11/14/02, Greg Macek wrote:
Thanks for the tip! Looks like I can change my date_archived field to
timestamp(8), since all I care about for this is the date information
(actual time is useless to me). My sql query all of a sudden got a lot
simpler. Thanks again for the help!
TIMEST
ally.
>
> Thanks,
>
> Matt
>
> Matthew P Baranowski
> Data Manager, Office of Educational Assessment
> University of Washington
>
> - Original Message -
> From: Greg Macek <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, N
Office of Educational Assessment
University of Washington
- Original Message -
From: Greg Macek <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 14, 2002 8:06 AM
Subject: INSERT INTO ... SELECT question
> Hello,
>
> I recently stumbled upon the INSERT IN
; >Hello,
> >
> >I recently stumbled upon the INSERT INTO..SELECT abilities. Basically
> >what I'm doing is archiving records into another table before deleting
> >them (inventory information). However, I'd like to have the archive
> >table to have one more f
Hello,
I recently stumbled upon the INSERT INTO..SELECT abilities. Basically
what I'm doing is archiving records into another table before deleting
them (inventory information). However, I'd like to have the archive
table to have one more field than the original table: a date_archive
e B but I wonder why
indexes are not up to date after the "insert into ... select" query
?
Thanks for you enlightenment :)
Kinds regards,
Alex.
-
Before posting, please check:
http://www.mys
insert ignore into select from
will skip duplicates.
Karl J. Stubsjoen wrote:
>Hello,
>
>How can I determin what erros occur from an "Insert into select from"
>statement? Namely, I'm looking for any duplicate record errors. It is
>important to inform the user (in
Hello,
How can I determin what erros occur from an "Insert into select from"
statement? Namely, I'm looking for any duplicate record errors. It is
important to inform the user (in a web browser) whether or not a particular
insert would fail.
Must I perform a lookup first, or is
> -Ursprüngliche Nachricht-
> Von: Ferdinand, Dieter
> Gesendet am: Donnerstag, 22. August 2002 10:56
> An: '[EMAIL PROTECTED]'
> Betreff: problem with null values by insert into .. select ..from ..
> join
>
> hello,
> i have one problem
This was resolved by adding an auto-increment column.
MySQL does not seem capable of coping with the situation below.
- Original Message -
From: "Dave" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, April 30, 2002 5:36 PM
Subject: INSERT INTO .
I am having problems with this INSERT INTO, below is a example. The SQL is
built in an ASP page and ASPVariable is, well the variable in the ASP page.
For some reason this will only insert the first row. In my test data the
SELECT alone returns 3 rows, but when added to the INSERT INTO only one
hi,
i have 2 tables:
currencyrates
+-+++
| code char(3)|| currency char(3) |
| name varchar(10)|| rate double|
| bcurrency char(1) |
FROM table list".
How can i solve this problem??
PS:This query works well when i don't add the condition after the first
condition in the where clause.
2)
i have a "read only table!" error message when i try to execute a "insert
into ...select"
Hello,
I'm relatively new to mysql and I've come
across a problem while trying to insert
values from
multiple tables into a single, existing
table. Here's the syntax that I'm using:
insert into table1 (col1,col2,col3,col4)
select Foo.col1, Foo.col2, Bar.col3,
Bunk.col4 from table2 as
Foo,
Hello,
I'm relatively new to mysql and I've come
across a problem while trying to insert
values from
multiple tables into a single, existing
table. Here's the syntax that I'm using:
insert into table1 (col1,col2,col3,col4)
select Foo.col1, Foo.col2, Bar.col3,
Bunk.col4 from table2 as
Foo,
At 12:53 PM +0800 10/3/01, chong wee siong wrote:
>Hi DBAs:
> I want to copy TableA in DatabaseA to TableB in DatabaseB
>with changes in the attributes of the tables, what options do I have?
>
>I tried these:
>
>a) insert into TableB (id) select (id) from DatabaseA.TableA;
>this works
>b) insert
Hi DBAs:
I want to copy TableA in DatabaseA to TableB in DatabaseB
with changes in the attributes of the tables, what options do I have?
I tried these:
a) insert into TableB (id) select (id) from DatabaseA.TableA;
this works
b) insert into TableB (id,data) select (id,data) from DatabaseA.Table
On Mon, 20 Aug 2001, Michiel Leegwater wrote:
> insert into table1 select Startnr, Tijd, Afstand, Slag, Datum, Opmerking,
> CRvan,CRtot,PR,Categorie from table2;
>
> This doesn't work, it says "Column count doesn't match value count at row 1"
> I understand the problem. But I can't use my ID colu
-
From: Michiel Leegwater [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 20, 2001 3:25 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Problem with INSERT INTO ... SELECT
Hello,
This is the situation:
Table1:
ID Startnr TijdAfstand SlagDatum Opmerking CRvan CRtot PR
Hello,
This is the situation:
Table1:
ID Startnr TijdAfstand SlagDatum Opmerking CRvan CRtot PR
Categorie
Table2:
Identical columns.
What is the problem? I'm trying to append all the values from table2 to
table1.
I was trying this SQL query:
insert into table1 s
47 matches
Mail list logo
