INSERT INTO NEW_TABLE (select old.x,sum(old.y) from OLD_TABLE old group by
> old.a)
> on duplicate key
> update b=sum(old.y);
The following I should work for you...
mysql> CREATE TABLE NEW_TABLE (
-> `a` varchar(10),
-> `b` double
-> ) engine=MyISAM;
Query OK,
Sorry, that was just a typo,
should have been
INSERT INTO NEW_TABLE (select old.x,sum(old.y) from OLD_TABLE old group by
old.x)
on duplicate key
update b=sum(old.y);
but this gives
ERROR (HY000): Invalid use of group function
INSERT INTO NEW_TABLE (select old.x,sum(old.y) from
t; > Is it possible to do an insert into with subselect and group by with an
> > additional on duplicate insert ?
> >
> > CREATE TABLE NEW_TABLE (
> > `a` varchar(10),
> > `b` double
> > ) engine=MyISAM;
> >
> >
> > INSERT INTO NEW_TAB
`a` varchar(10),
> `b` double
> ) engine=MyISAM;
>
>
> INSERT INTO NEW_TABLE (select old.x,sum(old.y) from OLD_TABLE old group by
> old.a)
> on duplicate key
> update b=sum(old.y);
>
> I get invalid group by clause on that.
>
> Currently I achieve the same
Is it possible to do an insert into with subselect and group by with an
additional on duplicate insert ?
CREATE TABLE NEW_TABLE (
`a` varchar(10),
`b` double
) engine=MyISAM;
INSERT INTO NEW_TABLE (select old.x,sum(old.y) from OLD_TABLE old group by
old.a)
on duplicate key
update b=sum
Scott Haneda wrote:
Is it possible, in single-line inserts, with on duplicate key clauses, to
get back a list of last insert id's for what was inserted?
I get strange results, just one single insert id, which makes sense from the
perspective of what was just inserted, however, I need to
Is it possible, in single-line inserts, with on duplicate key clauses, to
get back a list of last insert id's for what was inserted?
I get strange results, just one single insert id, which makes sense from the
perspective of what was just inserted, however, I need to know what the
returned i
x27;t want to change *any* of the other columns (such as the
> date for the hit or the id, etc.)
Perhaps I'm wrong here, but my suggestion should still work wouldn't it?
"insert into table1 (hits) values (hits+1) on duplicate key update;"
So on a new record, all columns ins
way?!
So instead of this cumbersome incantation that makes you want to rip out
your hair and puch your cube-mate dead in the nose:
insert into table1 (c1,c2,c3) values (v1,v2,v3) on duplicate key
update c1=v1, c2=v2, c3=v3;
Just allow a more sane and logical:
insert into table1 (c1,c2,c3) values
way?!
So instead of this cumbersome incantation that makes you want to rip out
your hair and puch your cube-mate dead in the nose:
insert into table1 (c1,c2,c3) values (v1,v2,v3) on duplicate key
update c1=v1, c2=v2, c3=v3;
Just allow a more sane and logical:
insert into table1 (c1,c2,c3) val
,
PROVINCE,
POSTAL_CODE,
COUNTRY,
CIRCUIT_ID,
TIME_ZONE
FROM ToBeUpdated
ON DUPLICATE KEY UPDATE
ID = VALUES(ID),
CONTACT_NAME = VALUES(CONTACT_NAME
I thought about it I was actually going to use merge tables AND
partitions to split the underlying MyISAM tables across two disks and
then partition on top.
It's POSSIBLE to use partitioning the way I want it but I'm going to
have to grok it for a bit more.
Thanks though.
Kevin
On 2/12
Kevin Burton wrote:
I want to use a merge table so that I can direct all new INSERTs to a
new merge table and migrate old data off the system by having a
continually sliding window of underlying MyISAM tables.
The problem is that of I do INSERT ... ON DUPLCATE KEY UPDATE and
that value isn't in
I want to use a merge table so that I can direct all new INSERTs to a
new merge table and migrate old data off the system by having a
continually sliding window of underlying MyISAM tables.
The problem is that of I do INSERT ... ON DUPLCATE KEY UPDATE and
that value isn't in the leading table wh
Hi,
From your query, understood that you want to retain old qty and new qty;
result in another field.
Try with,
INSERT INTO TABLE1 (id,newqty) values (6,300) ON DUPLICATE KEY UPDATE
totqty=oldqty+newqty, oldqty=newqty;
Thanks,
ViSolve DB Team
- Original Message -
From: "Ed
Ed Reed wrote:
I use On Duplicate Key Update a lot and I usually use it like this,
Insert Into tablename (myID, Qty)
Values (1034,15),
(6,13),
(5001,1),
(145,20) On Duplicate Key Update Qty=Values(Qty);
This works very well but now I'd like to do something a little
different. I'd li
Sorry for the premature question. I think I figured it out.
On Duplicate Key Update Qty=Qty+Values(Qty);
I haven't tested it yet but it makes sense that it'll work.
>>> "Ed Reed" <[EMAIL PROTECTED]> 1/5/07 2:40 PM >>>
I use On Duplicate Key
I use On Duplicate Key Update a lot and I usually use it like this,
Insert Into tablename (myID, Qty)
Values (1034,15),
(6,13),
(5001,1),
(145,20) On Duplicate Key Update Qty=Values(Qty);
This works very well but now I'd like to do something a little
different. I'd like to have a query
Hi,
I'm using DBI and a dsn with 'mysql_client_found_rows=0' appended.
A normal update returns 0E0 if no data has changed.
The update part of insert...on duplicate key update always returns
2, whether data has changed or otherwise, plus the timestamp
column is not automatical
ac_id="FAC-0002" ON DUPLICATE KEY UPDATE
FAC_ID="FAC-0003"
Now in my untrained mind, this should create 4 new, identical records with
only the fac_id field changed.
Where am I going wrong?
Thanks.
Chris Jones
14 Oneida Avenue
Toronto, ON M5J 2E3.
Tel. 416-203-7465
On Jun 30, 2006, at 10:44 AM, Rob Desbois wrote:
That leaves me with ON DUPLICATE KEY UPDATE. It's not amazingly
helpful as you have to provide a column to update - however I can
just say e.g.
ON DUPLICATE KEY UPDATE id=id
The problem with this is that if I then do "SELECT LAST
sn't there before and has been inserted,
returning the new value of id.
I don't want to perform an INSERT IGNORE as this ignores far more errors than
just duplicate keys.
I'd rather not use REPLACE as if the unique key matches then the rest of the
row definitely matches.
That leaves
An update - it also occurs in 4.1.19
Bug filed here:
http://bugs.mysql.com/bug.php?id=19978
Cheers,
Richard.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sent: Friday, 19 May 2006 12:41 PM
To: Richard Dale; mysql@lists.mysql.com
Subject: RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect?
Its not a problem with precision is it - 332.25 looks OK to you and me, but
who knows what it looks like to the CPU? Maybe it sees one of the doubles as
slig
.
To: mysql@lists.mysql.com
Subject: RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect?
> Most likely, MySQL has done a 'DELETE' followed by an 'INSERT' - i.e. two
rows.
According to:
http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html
The rows-affected v
what happens if the row is neither inserted nor updated? Right now it
appears to returns '2'. I think it should return '0'.
Cheers,
Richard.
-Original Message-
From: Richard Dale [mailto:[EMAIL PROTECTED]
Sent: Friday, 19 May 2006 11:23 a.m.
To: mysql@lists.mysql.com
Most likely, MySQL has done a 'DELETE' followed by an 'INSERT' - i.e. two rows.
Quentin
-Original Message-
From: Richard Dale [mailto:[EMAIL PROTECTED]
Sent: Friday, 19 May 2006 11:23 a.m.
To: mysql@lists.mysql.com
Subject: INESRT ... ON DUPLICATE KEY - rows affecte
.
INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES
(202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE
KEY UPDATE
open='334.25',high='334.25',low='334.25',close='334.25
Try:
INSERT *IGNORE* INTO table (a,b) VALUES (1,2)
--
Gabriel PREDA
Senior Web Developer
I want an On Duplicate Key do nothing feature which obviously doesn't
exist. However, will this be any faster than actually updating the row.
INSERT INTO table (a,b) VALUES (1,2)
ON DUPLICATE KEY UPDATE b=b;
BTW each row is made up of only 2 columns and those to columns make up
the k
Hello.
Perhaps you have forgotten to add col_name=expr to the end of your
query. See:
http://dev.mysql.com/doc/refman/5.0/en/insert.html
Jonathan Mangin wrote:
> I'm trying to change a couple of replace statements to
> insert...on duplicate key update (using Perl/DBI).
>
>
I'm trying to change a couple of replace statements to
insert...on duplicate key update (using Perl/DBI).
foreach my $key (keys %e_items) {
my $sql = "insert table1
(id, date, time, uid, type, seq, value)
values
(?, ?, ?, ?, ?, ?, ?)
s a "You have an error
in your SQL syntax." error, and not those specified in this bug report.
This means the SQL parser failed to understand its query.
Regards,
Jocelyn
Hi Siegfried,
I think you've run into bug #8732:
...
Description:
if you do a INSERT INTO table (col_list) SE
Hi Siegfried,
I think you've run into bug #8732:
...
Description:
if you do a INSERT INTO table (col_list) SELECT ... ON DUPLICATE KEY
UPDATE and
refer to the same column in the col_list and in the UPDATE clause, it
will bail
out, doesn't seem like it should, since not listing the
Hi,
Are you using MySQL-4.1 ? (ON DUPLICATE KEY syntax has been introduced
in 4.1)
Regards,
Jocelyn
Siegfried Heintze wrote:
Thanks for deciphering that terrible message, Shawn. I accidentally must
have hit the paste key too many times.
Anyway, here is my new insert statement:
INSERT
Thanks for deciphering that terrible message, Shawn. I accidentally must
have hit the paste key too many times.
Anyway, here is my new insert statement:
INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES
(211584,'2005-06-26',2) ON DUPLICATE KEY UPDATE cJobTitl
you add another command,
> > mysql> insert into bla1 values (NULL, "Cello3", NULL) on duplicate key
> update
> > whentime = NOW();
> > The right ID will be used.
>
> Yes, if I insert an new value then the ID column gets incremented. But if I
> try
On 19.05., [EMAIL PROTECTED] wrote:
> If you add another command,
> mysql> insert into bla1 values (NULL, "Cello3", NULL) on duplicate key update
> whentime = NOW();
> The right ID will be used.
Yes, if I insert an new value then the ID column gets incremented. But if I
Hi,
If you add another command,
mysql> insert into bla1 values (NULL, "Cello3", NULL) on duplicate key update
whentime = NOW();
The right ID will be used.
Since last_insert_id() has a connection scope, it's better for you to use :
select max(id) from bla1;
Mathias
Selon S
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `value` (`value`)
-> ) TYPE=MyISAM
-> ;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> insert into bla1 values (NULL, "Cello", NULL) on duplicate key update
whentime = NOW();
Query OK, 1 row af
Thanks for the verification.
After some experiments, I came up with an alternative using
"REPLACE INTO foo (...) SELECT ... UNION ..." to accomplish
the same task, so will use it for now.
INSERT INTO foo (id, value) SELECT id, value FROM bar
ON DUPLICATE KEY UPDATE value = valu
Hi!
On May 15, Taisuke Yamada wrote:
> Hi. I found behavior of "ON DUPLICATE KEY" bit odd, and would
> like to know if it's a bug or feature.
>
> I'm trying to "insert" or "add" (not "replace") data of one
> table to another table
Hi. I found behavior of "ON DUPLICATE KEY" bit odd, and would
(Blike to know if it's a bug or feature.
(B
(BI'm trying to "insert" or "add" (not "replace") data of one
(Btable to another table and came up with following statement:
(B
(B CR
Hi,
It could perhaps be related to bug #8147.
http://bugs.mysql.com/bug.php?id=8147
Regards,
Jocelyn
Aleksandr Guidrevitch a écrit :
Hi all !
I'm having problem with INSERT ... ON DUPLICATE KEY UPDATE on mysql 4.1.10
--8<--8<--8<--8<--8<--8<--8<-
Hi all !
I'm having problem with INSERT ... ON DUPLICATE KEY UPDATE on mysql 4.1.10
--8<--8<--8<--8<--8<--8<--8<--8<--8<
INSERT INTO lot_end (id, owner_id, category_id, title, current_price,
buy_price, end_time, price, bid_cou
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)
Re
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
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 duplicate key, could do this: update all the columns that are
Quoting sam wun <[EMAIL PROTECTED]>:
> HI,
>
> the following insert/update produced error. I m using MySQL 5.0.
> $insert_sql = qq {insert into inventory
> (prodcode,qty,lastupdatedate,prodname,basename,vendorname,cost)
> values (?,?,?,?
HI,
the following insert/update produced error. I m using MySQL 5.0.
$insert_sql = qq {insert into inventory
(prodcode,qty,lastupdatedate,prodname,basename,vendorname,cost)
values (?,?,?,?,?,?,?) ON DUPLICATE KEY
UPDATE
qty
Hi!
On Oct 15, Jason McManus wrote:
> Good afternoon,
>
> I have had reason to use the new (as of 4.1.1) INSERT .. ON DUPLICATE KEY
> UPDATE syntax in MySQL. However, I am a bit confused as to the return
> value. Issuing the INSERT .. ON DUP KEY UP statement, upon finding a
>
Good afternoon,
I have had reason to use the new (as of 4.1.1) INSERT .. ON DUPLICATE KEY
UPDATE syntax in MySQL. However, I am a bit confused as to the return
value. Issuing the INSERT .. ON DUP KEY UP statement, upon finding a
duplicate key and updating that record, mysql-client returns &q
Hi,
ON DUPLICATE KEY UPDATE is only available with MySQL 4.1.x.
Regards,
Jocelyn
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 08, 2003 8:32 AM
Subject: Insert...on duplicate key update
> >Description:
> I have b
ive syntax error messages centered on the 'on duplicate key update..' section
of the
statement.
>How-To-Repeat:
here is my statement in a simplified form, given directly to the database:
mysql> insert into connections (taskID, deadline, user) values ('001',7,'Bob
55 matches
Mail list logo
