Re: "Insert ... select ... On Duplicate Update" Question

[Perrin Harkins]

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 work that way.

It probably could, but it doesn't.  With the availability of the
information schema, it's easy to look up the columns in a table, so
doing this from a program is relatively simple.

- Perrin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: "Insert ... select ... On Duplicate Update" Question

[mos]

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 rows 
then a Replace will work fine (but it would also execute Delete/Insert 
triggers if I had any). But I have 50 million rows and will need to update 
maybe 1/2% of those, all of those deletes and inserts will slow things down.


Now logically I thought this should work:

insert into Table2 select * from table1 on duplicate key update;

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 work that way.  It looks like I have to re-specify 
each of the column names in Table1 in the Update clause as a column 
assignment. I thought this was totally necessary because MySQL knew the 
column assignments for the original insert, why couldn't it pick up where 
it left off and use the existing row (specified by the duplicate key value 
it found). Either that or just force me to specify the key value assignment 
in the Update clause and not the whole column list which could be 100 columns.


Mike



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 "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


Perrin,
  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. I'd like to use something like:


insert into Table2 select * from table1 on duplicate key update;

but this gives me a syntax error.

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near '' at line 1


So it is looking for an Update expression. I'd like it to update all the 
columns in the Select statement to the row with the matching key. After 
all, this is what the statement was trying to do in the first place. I 
don't see why I have to explicitly specify all of the value assignments 
in the On Duplicate phrase over again.


Mike
MySQL 5.0.24



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: "Insert ... select ... On Duplicate Update" Question

[Phil]

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 "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
>>
>
> Perrin,
>   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. I'd like to use something like:
>
> insert into Table2 select * from table1 on duplicate key update;
>
> but this gives me a syntax error.
>
> Error Code : 1064
> You have an error in your SQL syntax; check the manual that corresponds to
> your MySQL server version for the right syntax to use near '' at line 1
>
> So it is looking for an Update expression. I'd like it to update all the
> columns in the Select statement to the row with the matching key. After all,
> this is what the statement was trying to do in the first place. I don't see
> why I have to explicitly specify all of the value assignments in the On
> Duplicate phrase over again.
>
> Mike
> MySQL 5.0.24
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
Help build our city at http://free-dc.myminicity.com !

Re: "Insert ... select ... On Duplicate Update" Question

[mos]

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 it.  There is no way around specifying the columns.

- Perrin


Perrin,
 Ok thanks. I'll do that.

Mike 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: "Insert ... select ... On Duplicate Update" Question

[Perrin Harkins]

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 columns.

- Perrin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: "Insert ... select ... On Duplicate Update" Question

[mos]

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?

- Perrin


Perrin,
   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. I'd like to use something like:


insert into Table2 select * from table1 on duplicate key update;

but this gives me a syntax error.

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to use near '' at line 1


So it is looking for an Update expression. I'd like it to update all the 
columns in the Select statement to the row with the matching key. After 
all, this is what the statement was trying to do in the first place. I 
don't see why I have to explicitly specify all of the value assignments in 
the On Duplicate phrase over again.


Mike
MySQL 5.0.24 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: "Insert ... select ... On Duplicate Update" Question

[Perrin Harkins]

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 list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

"Insert ... select ... On Duplicate Update" Question

[mos]

Is there a way to get "Insert ... select  ... On Duplicate Update" to 
update the row with the duplicate key? Otherwise I'll have to use Replace 
which is inefficient because it deletes the old duplicated row and then 
inserts the new row with the same key. I'd much rather have it update the 
existing row with the same existing keys, but use the new values in the 
Select statement. Why can't it do this? It would be much faster.


Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]