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 doe
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 ro
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 t
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 d
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 co
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
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
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 tbl_1(fld
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
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 stat
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.
tbl_1(fld1,fld2,fl
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 auto_inc
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)
SELEC
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 value
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 co
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 co
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" <[
The first to generate you sequence, then
second to populate the main table.
- Original Message -
From: "Ed Reed" <[EMAIL PROTECTED]>
To:
Sent: Friday, January 12, 2007 8:22 PM
Subject: Re: Insert ... Select troubles
Thanks for the suggestion Brent. The auto_increment won&#
to generate you sequence, then
second to populate the main table.
- Original Message -
From: "Ed Reed" <[EMAIL PROTECTED]>
To:
Sent: Friday, January 12, 2007 8:22 PM
Subject: Re: Insert ... Select troubles
Thanks for the suggestion Brent. The auto_increment won'
auto increment sequence to be made
for each record "group".
- Original Message -
From: "Ed Reed" <[EMAIL PROTECTED]>
To:
Sent: Friday, January 12, 2007 12:42 PM
Subject: Re: Insert ... Select troubles
> ItemCount is essentially a counter of the records fr
made for each
record "group".
- Original Message -
From: "Ed Reed" <[EMAIL PROTECTED]>
To:
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, e
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 PROTECTE
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 me!
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
in
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
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 YES_CO
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)
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]> wro
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
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 |
>
- Original Message -
From: "Aragorn" <[EMAIL PROTECTED]>
To:
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 applications A and
B
>
>
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
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 t
[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 usin
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 [LOW_
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 s
Wednesday, 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...
> inse
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 sy
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
res me to alter 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: IN
dd 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
tterley [mailto:[EMAIL PROTECTED]
> Sent: Monday, 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*). :
te
the query based 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'; &#
ion, 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 ..
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,
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_
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
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
of course, the syntax is:
insert into db3.table2 (column list) select 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, Octo
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:
ROTECTED]>
Sent: Tuesday, September 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
>
* 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, a
>
> - Original Message -
> From: "Stefan Kuhn" <[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. The
TED]>
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 the records (the
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 Alejan
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 record
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 (ascendig
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 arbitrary
[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 I
ks again
>
> //Lars Rasmussen
>
> -Oprindelig meddelelse-
> Fra: Jay 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 I
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
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, Febru
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
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":
http://www.mysql.com/
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
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
>> VA
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 somethin
Thanks for the lead Jeff, maybe you can elaborate?
We need to essentially do this:
Insert into orders (number, comment) select max(number) + 1, 'comment' from
orders where user = 'user_name';
(Not supported in MySQL)
Another user suggested using a select first to get the value, then doing an
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 exa
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 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
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 J
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 AUTO_INCREM
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
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: <[EMA
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
"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 tabl
79 matches
Mail list logo
