Re: Error: You can't specify target table '...' for update in FROM clause

2008-02-07 Thread Yves Goergen 

On 07.02.2008 03:52 CE(S)T, Chris wrote:
If you don't mind a mysql-specific fix, and can get the data you want 
from a select query you could:


insert into table (select goes here) on duplicate key update;

or maybe a replace into ?


INSERT/REPLACE ... SELECT will always overwrite the entire row, but I 
only want to copy a single column of it. The rest of the record must 
remain intact. So I can't use that, too.


I also try to avoid DBMS-specific workarounds where I can in this 
project. So maybe one day MySQL will drop the above mentioned 
restriction. :)


--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
Visit my web laboratory at http://beta.unclassified.de

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

Re: Error: You can't specify target table '...' for update in FROM clause

2008-02-06 Thread Yves Goergen 

On 06.02.2008 08:12 CE(S)T, Chris wrote:

Yves Goergen wrote:
My goal was to copy some potentially large BLOB from one record to 
another in the same table



Update table set blob2_field=blob1_field;


This does something totally different. ;) See my first posting why.

--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
Visit my web laboratory at http://beta.unclassified.de

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

RE: Error: You can't specify target table '...' for update in FROM clause

2008-02-06 Thread Jerry Schwartz 
 -Original Message-
 From: Yves Goergen [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, February 05, 2008 6:22 PM
 To: Baron Schwartz
 Cc: mysql@lists.mysql.com
 Subject: Re: Error: You can't specify target table '...' for update in
 FROM clause

 On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote:
  You can't select from a table you're updating at the same time.  What
  at the same time means is a bit unclear unless you're one of the
  MySQL developers ;-)

 Yes, Paul DuBois already replied to me off-list. Now I found that
 documentation part and understand that MySQL cannot do this. (Haven't
 tested whether other DBMS can, would be pointless anyway.)

 My goal was to copy some potentially large BLOB from one record to
 another in the same table, nothing more. I have now chosen the way to
 fetch it from the database and have my application just write it back
 again. I wanted to avoid this unnecessary copying around.
[JS] I don't know if this is more efficient that copying the data to a program 
and pushing it back, but you can use a few statements to use a temporary table 
of your own. I often do this so that I can accomplish what I want without 
writing a program at all.





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

Re: Error: You can't specify target table '...' for update in FROM clause

2008-02-06 Thread Chris 

Yves Goergen wrote:

On 06.02.2008 08:12 CE(S)T, Chris wrote:

Yves Goergen wrote:
My goal was to copy some potentially large BLOB from one record to 
another in the same table



Update table set blob2_field=blob1_field;


This does something totally different. ;) See my first posting why.


Ah I missed the first post.

If you don't mind a mysql-specific fix, and can get the data you want 
from a select query you could:


insert into table (select goes here) on duplicate key update;

or maybe a replace into ?


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

Error: You can't specify target table '...' for update in FROM clause

2008-02-05 Thread Yves Goergen 

Hi,

I've got an error message from MySQL 5.0 that I don't understand.

UPDATE message_revision SET HasData = 1, Data = (SELECT Data 
FROM message_revision WHERE MessageId = 7 AND RevisionNumber = 5) 
WHERE MessageId = 7 AND RevisionNumber = 6


SQL error: [SQLSTATE:HY000, 1093] You can't specify target table 
'message_revision' for update in FROM clause


What went wrong?

--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
Visit my web laboratory at http://beta.unclassified.de

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

Re: Error: You can't specify target table '...' for update in FROM clause

2008-02-05 Thread Baron Schwartz 
Hi,

On Feb 5, 2008 11:26 AM, Yves Goergen [EMAIL PROTECTED] wrote:
 Hi,

 I've got an error message from MySQL 5.0 that I don't understand.

 UPDATE message_revision SET HasData = 1, Data = (SELECT Data
 FROM message_revision WHERE MessageId = 7 AND RevisionNumber = 5)
 WHERE MessageId = 7 AND RevisionNumber = 6

 SQL error: [SQLSTATE:HY000, 1093] You can't specify target table
 'message_revision' for update in FROM clause

 What went wrong?

You can't select from a table you're updating at the same time.  What
at the same time means is a bit unclear unless you're one of the
MySQL developers ;-)  However, you can do multi-table updates like
this:

UPDATE tbl AS a
  INNER JOIN tbl AS b ON 
  SET a.col = b.col

If you absolutely need the subquery, there's a workaround, but it's
ugly for several reasons, including performance:

UPDATE tbl SET col = (
   SELECT ... FROM (SELECT FROM) AS x);

The nested subquery in the FROM clause creates an implicit temporary
table, so it doesn't count as the same table you're updating.

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

Re: Error: You can't specify target table '...' for update in FROM clause

2008-02-05 Thread Yves Goergen 

On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote:

You can't select from a table you're updating at the same time.  What
at the same time means is a bit unclear unless you're one of the
MySQL developers ;-)


Yes, Paul DuBois already replied to me off-list. Now I found that 
documentation part and understand that MySQL cannot do this. (Haven't 
tested whether other DBMS can, would be pointless anyway.)


My goal was to copy some potentially large BLOB from one record to 
another in the same table, nothing more. I have now chosen the way to 
fetch it from the database and have my application just write it back 
again. I wanted to avoid this unnecessary copying around.



 However, you can do multi-table updates like
this:

UPDATE tbl AS a
  INNER JOIN tbl AS b ON 
  SET a.col = b.col


That sounds interesting, however, I couldn't find it in PostgreSQL's and 
SQLite's reference. Is this a MySQL extension over the SQL standard?


--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
Visit my web laboratory at http://beta.unclassified.de

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

Re: Error: You can't specify target table '...' for update in FROM clause

2008-02-05 Thread Chris 

Yves Goergen wrote:

On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote:

You can't select from a table you're updating at the same time.  What
at the same time means is a bit unclear unless you're one of the
MySQL developers ;-)


Yes, Paul DuBois already replied to me off-list. Now I found that 
documentation part and understand that MySQL cannot do this. (Haven't 
tested whether other DBMS can, would be pointless anyway.)


My goal was to copy some potentially large BLOB from one record to 
another in the same table, nothing more. I have now chosen the way to 
fetch it from the database and have my application just write it back 
again. I wanted to avoid this unnecessary copying around.


Update table set blob2_field=blob1_field;

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