At 08:23 PM 7/20/2008, Perrin Harkins wrote:
On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote:
Is there a way to get Insert ... select ... On Duplicate Update to
update
the row with the duplicate key?
That's what it does.
Why can't it do this?
What makes you think it can't?
On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote:
I can't specify all of the columns in a Set statement in the
OnDuplicate clause because I don't know what the column names are and there
could be 100 columns.
Write code to do it. There is no way around specifying the
At 11:00 AM 7/21/2008, Perrin Harkins wrote:
On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote:
I can't specify all of the columns in a Set statement in the
OnDuplicate clause because I don't know what the column names are and there
could be 100 columns.
Write code to do
So just use REPLACE instead of INSERT...
http://dev.mysql.com/doc/refman/5.0/en/replace.html
On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote:
At 08:23 PM 7/20/2008, Perrin Harkins wrote:
On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote:
Is there a way to get
At 12:16 PM 7/21/2008, you wrote:
So just use REPLACE instead of INSERT...
Sure, but a Replace will delete the existing row and insert the new one
which means also maintaining the indexes. This will take much longer than
just updating the existing row. Now if there were only a couple of
On Mon, Jul 21, 2008 at 2:43 PM, mos [EMAIL PROTECTED] wrote:
I thought if MySQL found a duplicate key on the insert, it would
automatically update the existing row that it found with the results from
table1 if I left out the column expressions in the update clause. But
apparently it doesn't
On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote:
Is there a way to get Insert ... select ... On Duplicate Update to update
the row with the duplicate key?
That's what it does.
Why can't it do this?
What makes you think it can't?
- Perrin
--
MySQL General Mailing List
For
On Thu, 3 Apr 2008, Hiep Nguyen wrote:
hi all, i have a question on insert ... select statement.
tbl_1(fld1,fld2,fld3, )
fld1 int primary key auto_increment not null
tbl_2(fld_a,fld_b,fld_c,...)
how do i construct my select statement so that fld1 is auto increment?
insert into
Hi Hiep,
you can put in either xxx = NULL
or you can skip it completely:
insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2;
Regards,
/Johan
Hiep Nguyen skrev:
hi all, i have a question on insert ... select statement.
tbl_1(fld1,fld2,fld3, )
fld1 int primary key
On Thu, 3 Apr 2008, Johan Höök wrote:
Hi Hiep,
you can put in either xxx = NULL
or you can skip it completely:
insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2;
Regards,
/Johan
Hiep Nguyen skrev:
hi all, i have a question on insert ... select statement.
Hi Hiep,
Hiep Nguyen skrev:
On Thu, 3 Apr 2008, Johan Höök wrote:
Hi Hiep,
you can put in either xxx = NULL
or you can skip it completely:
insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2;
Regards,
/Johan
Hiep Nguyen skrev:
hi all, i have a question on insert ... select
The beauty of this language is exactly as Johan says, you can skip the
obvious, Just insert all the other non-obvious columns. In the event that
you have numerous defaulted columns, though, it's best to supply a NULL so
the syntax is parallel (IMO), or alternatively to name the columns. But
either
Ed Reed wrote:
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative
Thanks Jay,
I had to make a change to the first part of the query to get the results that I
wanted but your suggestion was definitely what I needed to get to the solution.
Thanks again.
For those that are interested, here's the final solution,
INSERT INTO purchase (Source, Item, Qty)
Skip the whol SELECT part an create a unique index on the fields you want unique (AttributeID, Attribute_Value). Then just do an
INSERT IGNORE. The index will prevent a new non-unique from being entered and the IGNORE will prevent an error.
- Original Message -
From: Miles Thompson
So with a unique index on ItemI + AttributeID + Attribute_Value, this could
be the
statement:
INSERT INTO bm_ItemsAttributes (ItemID, AttributeID, Attribute_Value) IGNORE
SELECT DISTINCT(bmIA1.ItemID) FROM bm__ItemsAttributes bmIA1, '31',
'default text';
which should result in a new row
So with a unique index on ItemI + AttributeID + Attribute_Value, this could
be the
statement:
INSERT INTO bm_ItemsAttributes (ItemID, AttributeID, Attribute_Value) IGNORE
SELECT DISTINCT(bmIA1.ItemID) FROM bm__ItemsAttributes bmIA1, '31',
'default text';
which should result in a new row
.
- Original Message -
From: Ed Reed [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, January 12, 2007 8:22 PM
Subject: Re: Insert ... Select troubles
Thanks for the suggestion Brent. The auto_increment won't work in my situation
though. My t1 table has a RecID field that already
, then
second to populate the main table.
- Original Message -
From: Ed Reed [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, January 12, 2007 8:22 PM
Subject: Re: Insert ... Select troubles
Thanks for the suggestion Brent. The auto_increment won't work in my situation though. My
ItemCount is essentially a counter of the records from the select
statement. So, every new INSERT ... SELECT statement gets a new
GroupCount (the next number up) and ItemCount represents the ID of the
items in that new group.
Does that make sense?
- Thanks
Michael Dykman [EMAIL PROTECTED]
to be made for each
record group.
- Original Message -
From: Ed Reed [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, January 12, 2007 12:42 PM
Subject: Re: Insert ... Select troubles
ItemCount is essentially a counter of the records from the select
statement. So, every new INSERT
record group.
- Original Message -
From: Ed Reed [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, January 12, 2007 12:42 PM
Subject: Re: Insert ... Select troubles
ItemCount is essentially a counter of the records from the select
statement. So, every new INSERT ... SELECT
Hi Barry,
see: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
you cannot insert into a table you're doing select on
(same goes for update).
Regards,
/Johan
Barry skrev:
I get this error:
Fehler in
/home/virtual/site4/fst/var/www/html/adminheaven/artikel-vererben-save.php
Johan Höök schrieb:
Hi Barry,
see: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
you cannot insert into a table you're doing select on
(same goes for update).
But i am doing it on a test server version 5.x and it works like a charm :)
--
Smileys rule (cX.x)C --o(^_^o)
Dance for
Yesmin Patwary [EMAIL PROTECTED] wrote on 03/22/2006 09:56:20 AM:
Dear All,
First of all, I would like to thank Shawn Green, Peter Brawley and
Josh for their kind help in my previous issue.
I have a table named master_list with two field customer_id and
list_code. I need to insert
Hi,
Try setting the default value of the column to 'Yes' that should work or
INSERT INTO Allocations(Project_ID, User_ID, field) SELECT P.Project_ID,
U.User_ID, 'Yes' FROM Users U, Projects P, Clients C WHERE P.Client_ID =
C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id)
Hi Shaun,
You could use the following statement:
INSERT INTO Allocations(Project_ID, User_ID, YES_COLUMN)
SELECT P.Project_ID, U.User_ID, 'Yes'
FROM Users U, Projects P, Clients C
WHERE P.Client_ID = C.Client_ID
AND U.Client_ID = C.Client_ID
AND Project_ID =.$project_id)
Please replace
Sorry guys,
Worked it out, if anyone is interested here is the query:
INSERT INTO Allocations(Project_ID, User_ID)
SELECT P.Project_ID, U.User_ID
FROM Users U, Projects P, Clients C
WHERE P.Client_ID = C.Client_ID
AND U.Client_ID = C.Client_ID
AND Project_ID = 2
Shaun [EMAIL PROTECTED] wrote in
Andy Pieters [EMAIL PROTECTED] wrote on 05/11/2005 04:41:05 PM:
Hi all
I want to 'copy' the contents of the table 'cart', where userid=... to
the
table pending_cart.
Here is some example data for the table 'cart'
++++-+
| id | userid | prodid | qty |
Hi
I got a reply offlist from Shawn Green telling me to check my indexes on table
pending_cart.
As it turned out, the primary index (id) did not have its auto_increment bit
set.
ALTER TABLE `pending_cart`
CHANGE `id`
`id` INT( 11 )
DEFAULT '0'
NOT NULL
- Original Message -
From: Aragorn [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, March 12, 2005 1:47 AM
Subject: insert - select from two different applications race condition?
I have a simple problem...
I'm using mysql 4.1.9/innodb table and two small python
Thanks Shawn, Sergei.
I'll get onto the new version as soon as I can.
Tom.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi!
On Feb 24, Tom Cunningham wrote:
It appears you can't combine an insert-select with an on-duplicate-key-update.
You can, since 4.1.10
(and there're some problems with name resolution there, so better wait
for 4.1.11 - search bugdb for details)
Regards,
Sergei
--
__ ___ ___
Tom Cunningham [EMAIL PROTECTED] wrote on 02/24/2005 11:31:31 AM:
It appears you can't combine an insert-select with an
on-duplicate-key-update.
I would find it very useful if you *could* do this. I know it would be
complicate how you would handle the syntax for what to do when you hit
a
On Tue, 2005-02-15 at 17:56, steve cooley wrote:
How do you you get the next auto increment value? I think I'm running
into the insert...select problem. I want to duplicate records from one
table to the _same_ table, with an incremented auto increment value.
Can I do something like this:
[snip]
mysql INSERT INTO Employee.tblNiiEmployee (empEmail)
- SELECT b.Email_Address
- FROM Employee.tblNiiEmployee a LEFT OUTER JOIN UserDB.Corporate b
- ON(a.empUsrName = b.Unix_Username)
- WHERE b.Email_Address IS NOT NULL;
Since versions prior to 4.0.14 do not support using
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 the
, September 01, 2004 10:06 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: insert/select for multiple tables...
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
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
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
Can you roughly outline the schema of the two tables?
If the key value doesn't have to match from the input to the final result,
you can always insert into a list of fields, skipping the auto_increment/key
column, and they will continue to be generated..
Assuming you have two tables with id_col,
was hoping that MySQL had
something built in to make this easier.
Thanks,
Eric
-Original Message-
From: Matt Chatterley [mailto:[EMAIL PROTECTED]
Sent: Monday, March 29, 2004 2:11 PM
To: 'Eric J. Janus'; 'MySQL'
Subject: RE: INSERT ... SELECT question
Importance: Low
Can you
on the table definition, but I was hoping that MySQL had
something built in to make this easier.
Thanks,
Eric
-Original Message-
From: Matt Chatterley [mailto:[EMAIL PROTECTED]
Sent: Monday, March 29, 2004 2:11 PM
To: 'Eric J. Janus'; 'MySQL'
Subject: RE: INSERT ... SELECT question
, March 29, 2004 2:25 PM
To: 'Eric J. Janus'; 'MySQL'
Subject: RE: INSERT ... SELECT question
Importance: Low
Oh, if only there were views!! That would make this easy. Maybe soon
(*please*). :)
Another approach (more cumbersome) might be to insert the rows you need to
duplicate into a temporary
an appropriate
where-clause to the above statement.
/Henrik
-Original Message-
From: Eric J. Janus [mailto:[EMAIL PROTECTED]
Sent: den 29 mars 2004 21:28
To: Matt Chatterley; 'MySQL'
Subject: RE: INSERT ... SELECT question
Views would be nice. :)
That idea sounds like it would work
the application everytime I change the
table, which I don't always want to do.
-Original Message-
From: Henrik Schröder [mailto:[EMAIL PROTECTED]
Sent: Monday, March 29, 2004 3:07 PM
To: 'Eric J. Janus'; 'MySQL'
Subject: RE: INSERT ... SELECT question
Importance: Low
Luis Lebron [EMAIL PROTECTED] wrote:
I am try to copy a couple of fields from one table to another. Here is my
query
Insert into users_test (firstName, lastName) Select firstName, lastName from
users;
The users table has 1,263 rows. However, only 1 row is inserted into
users_test.
I use this syntax but I have privilege problem.
Thenk you anyway
- Original Message -
From: Nitin [EMAIL PROTECTED]
To: IEEIO AAOOCO [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, October 27, 2003 5:20 PM
Subject: Re: Insert ... Select question
of course, the syntax is:
insert
of course, the syntax is:
insert into db3.table2 (column list) select column list from db1.table1
for more information have a look at
http://www.mysql.com/doc/en/INSERT_SELECT.html
Enjoy
Nitin
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, October
Nikos,
Yes. Use the following syntax:
INSERT INTO db_name.tbl_name (col, col, col) SELECT col, col, col FROM
db_name.tbl_name;
Regards,
Adam
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, October 27, 2003 9:58 AM
To: [EMAIL PROTECTED]
Subject: Insert
Hi,
well, I'm not totally sure about your question. Which order is reversed ? The
order you get the entries with a select after the insert ? If it is this,
then I think it's not a problem with the insert. The order is then given by
the select, and if no order by is in the select, it is
Hi Stephan,
Let's see the case :
I use ORDER BY, because I want that order in PTemp
table, so I do not have to order them later (because
they are retrieved several times later).
1. Inserting with mysql c.l.i. :
I get the records well sorted : first by a, secondly
by b and finally by c
Hi Alejondro
I use ORDER BY, because I want that order in PTemp
table, so I do not have to order them later (because
they are retrieved several times later).
If I understand correct ,,,
U can never trust the order ur records get return from DB
(it is indepented of the order u insert records
Hi,
I think you can't do this. There is no order in the table, so there is no
point in using order by with insert. You always have to do this when
retrieving the records (the order you get with select without order by is
accidential).
HTH
Stefan
Am Tuesday 02 September 2003 11:49 schrieb
02, 2003 6:57 AM
Subject: Re: insert ... select .. order by, problem
Hi,
I think you can't do this. There is no order in the table, so there is no
point in using order by with insert. You always have to do this when
retrieving the records (the order you get with select without order
[EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 6:57 AM
Subject: Re: insert ... select .. order by, problem
Hi,
I think you can't do this. There is no order in the table, so there is no
point in using order by with insert. You always have to do this when
retrieving
* Albert
Stefan,
I'm Roger, but I reply anyway. :)
Do you imply that tables cannot be sorted desc or asc based on one of the
columns e.g. a last name? or am I misunderstanding you.
In relational database theory the order of rows within the table is
undefined, i.e. it is up to the server, and
02, 2003 8:10 AM
Subject: Re: insert ... select .. order by, problem
Hi Albert,
you are not misunderstanding me :-) Tables can indeed not be sorted, it's
output which gets sorted. The difference is not academic, but important:
It's
not the table which gets an order, but the output. Take
Roger,
Thanks for the additional clarification
Albert
Atlanta
- Original Message -
From: Roger Baklund [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Albert [EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 8:00 AM
Subject: Re: insert ... select .. order by, problem
* Albert
Blanchard [mailto:[EMAIL PROTECTED]
Sendt: 13. august 2003 19:59
Til: Lars Rasmussen; [EMAIL PROTECTED]
Emne: RE: INSERT SELECT
[snip]
I used this command:
INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id
in($numbers)
But now it gives this error:
Column count doesn't match
[snip]
I used this command:
INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id
in($numbers)
But now it gives this error:
Column count doesn't match value count at row 1
[/snip]
* does not return a specific number of columns, the work around is to
specify the columns explicitly
INSERT
The typical syntax for this would be:
INSERT INTO phone (personID, phoneNr, email, type)
SELECT personID, '1-xxx-xxx-', \N, 'OFF'
FROM persons WHERE lastName=''
HTH,
Tore.
- Original Message -
From: Kyle Lange [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, February
That makes sense.
Thank you Tore.
Kyle
-Original Message-
From: Tore Bostrup [mailto:[EMAIL PROTECTED]]
Sent: Friday, 21 February 2003 01:12
To: Kyle Lange; [EMAIL PROTECTED]
Subject: Re: INSERT ... SELECT - A slightly different question
The typical syntax for this would be:
INSERT
On Thursday 19 December 2002 19:19, Andrew Kuebler wrote:
When I run an INSERT. SELECT query and I am inserting the records into a
new table that has a UNIQUE key, the statement will fail as soon as it
hits a Duplicate entry error. No further entries will be inserted. The
only way I know to
At 12:19 -0500 12/19/02, Andrew Kuebler wrote:
When I run an INSERT. SELECT query and I am inserting the records into a
new table that has a UNIQUE key, the statement will fail as soon as it
hits a Duplicate entry error. No further entries will be inserted. The
only way I know to get around this
On 19 Dec 2002, at 12:19, Andrew Kuebler wrote:
When I run an INSERT. SELECT query and I am inserting the records into a
new table that has a UNIQUE key, the statement will fail as soon as it
hits a Duplicate entry error.
You probably want to use INSERT IGNORE:
Hi SImon
Try this
INSERT INTO
table1
SELECT
12 as fixedvalue1 , 20 as fixedvalue2 ,table2.A , table2.B
FROM
table2
WHERE
table2.id = 10
First the SELECT Query is executed from the table 2 which has id = 10
The Result would be
Simon,
Thursday, July 18, 2002, 1:21:20 PM, you wrote:
I am trying to copy data from one table to another using in MySQL
INSERT INTO table1
SELECT table2
statment but I would like to add some fixed values to this and so some
thing like.
INSERT INTO table1
SELECT table2
VALUES ('A','B');
UPDATE sequence_table
SET sequence = LAST_INSERT_ID(sequence + 1)
Further calls to LAST_INSERT_ID on the same connection will return the value
sequence + 1. It's connection specific, so multiple connections can update
your sequence table without interfering with each other.
There's a great
use an auto_increment column.
Or if this is during an UPDATE
UPDATE tbl SET col = col + 1 WHERE ..;
Allon Bendavid wrote:
Hello All-
I just read that an Insert ... Select naming the same table is not supported
in MySQL.
What is the alternative? What we need to do is simply insert
Hi!,
INSERT INTO ... SELECT statement works in MySQL. The web site is :
http://www.mysql.com/doc/I/N/INSERT_SELECT.html
I have no idea what you meant by it doesn't work for the same tables..
But as the answer to your question, you could do something like:
SELECT @a:=MAX(fieldname) FROM TABLE;
use LAST_INSERT_ID() to get the last inserted id
http://www.mysql.com/doc/G/e/Getting_unique_ID.html
and
'The most recently generated ID is maintained in the server on a
per-connection basis. It will not be changed by another client. It will not
even be changed if you update another
SELECT LAST_INSERT_ID() will return the last auto_increment key written. This value
is connection-specific, so that inserts performed on other connections will not affect
it (in other words, you'll get the last key generated by user 1, regardless of what
user 2 does in the meantime).
--Greg
At 4:51 PM -0700 10/31/01, Nathan wrote:
I have a table(2) with a different structure than the table(1) I'm
selecting from. I want to be able
to grab some info from table1, put it in table2, along with some
variables from my php page. Is it
possible to do like:
INSERT INTO table2 VALUES 1,2,3
At 8:44 PM -0500 9/9/01, Marcus Young wrote:
Hi,
I'm fairly new to MySQL. I'm trying to insert data into a table
where one field is derived from a SELECT on another table (a key)
and the other fields are defined directly (eg field_01=abcd) .
The formats I have been trying don't appear to be
Try to look at page 442 to 446 of the mySQL manual, it may give some help
on your field creation using insert select.
Regards,
Calvin Chin
[EMAIL PROTECTED]
Ipmuda Berhad - Building materials for tomorrow's world !
-Original Message-
From: Marcus Young [EMAIL PROTECTED]
To: [EMAIL
"Martin Jeremic" [EMAIL PROTECTED] wrote:
Can somebody tell me where i'm wrong ?
insert into table3 select * from table1 where RecNo = 12 --- this
works
insert into table3 select * from table2 where RecNo = 12 --- Nothing
Happens
Post to the list the output of:
DESCRIBE table1
SELECT
78 matches
Mail list logo
