[snip]
I read 6) as saying that until you complete all of the calculations on a
row, you don't change the data for the row. I could even interpret this
as saying that you don't update ANY row on the table until you have
completed the calculations for ALL of the rows.
I read 7) as saying that you
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004
05:20:49 PM:
> [snip]
> > Anyhow, it has been a good discussion. No bugs were uprooted and
> > everyone's understanding may have risen a notch. After a couple of
> > decades using SQL I still find interesting topics to discuss.
>
> Yes, I t
[snip]
...tons o' stuff...
[/snip]
BTW...Happy New Year!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[snip]
> Anyhow, it has been a good discussion. No bugs were uprooted and
> everyone's understanding may have risen a notch. After a couple of
> decades using SQL I still find interesting topics to discuss.
Yes, I too have enjoyed this conversation. Thank you for thinking out
loud with me on such
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004
03:50:24 PM:
> [snip]
> ... a bunch of stuff
> [/snip]
>
> It alomost sounds as if you're coming from a M$ campwhat you
> describe are VIEWS.
In a way it is, an internally generated view of the source/destination
tables.
>
> Any
[snip]
... a bunch of stuff
[/snip]
It alomost sounds as if you're coming from a M$ campwhat you
describe are VIEWS.
Anyhow, the ANSI SQL standards specify some of the behaviors we have
been talking about. Many DB manufacturers use the standards and
implement other "features" like you des
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004
01:32:20 PM:
> [snip]
> I understand that is the behavior of the assignment operator. That's why
> I used it. ;-) I expected the results to be 19 and 6 not 19 and 20.
> This is just another example of the serialization of evaluation that
>
[snip]
I understand that is the behavior of the assignment operator. That's why
I used it. ;-) I expected the results to be 19 and 6 not 19 and 20.
This is just another example of the serialization of evaluation that
occurs while processing a single row of data. I have the opinion that
while proce
[snip]
So the question is not, if a multi table update should be implemented
serialized. The answer is clear: yes it should.
The question is, on which data the subsequent updates should work.
[/snip]
It should work on the data specified in the update query. Each set
statement is isolated and the
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004
11:43:43 AM:
> [snip]
> I hope I am not so naive that Kai and I are the only two who sees a
> problem with this behavior. Some of you may think this is a "feature"
> but I do not like it because that means that calculational errors will
> o
best. On
several other implementations (other DBs) of multi-table updates the
behavior is exactly the same as this.
I don't think it is clear, what Shawn's and my point is.
It is clear, that a multi table update will be executed in a serialized
manner. I don't want to think of the di
[snip]
I hope I am not so naive that Kai and I are the only two who sees a
problem with this behavior. Some of you may think this is a "feature"
but I do not like it because that means that calculational errors will
occur simply by inverting column positions (putting one before the
other) within a
[EMAIL PROTECTED] schrieb:
I snipped the discussion down to the SQL-statements with some
explanational text. I hope it does not break reading.
[snip]
UPDATE table1
INNER JOIN table2 ON table2.ID_table1=table1.ID
SET table2.value=table2.value-IF(table1.condition=7,1,0),
table1.condition=8
WHERE
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004
07:59:35 AM:
> [snip]
> "atomically" from my applications point of view means:
> I have a snapshot of my data (due to InnoDB this does not change
> hiddenly during
> my session). I send a query to the database. I get the result / changed
> d
[snip]
"atomically" from my applications point of view means:
I have a snapshot of my data (due to InnoDB this does not change
hiddenly during
my session). I send a query to the database. I get the result / changed
data.
And everything between sending and the receiving the result I expect to
be
han
Zitat von Jay Blanchard <[EMAIL PROTECTED]>:
> [snip]
> Although my question goes in that direction, it is more about why this
> statement
> does not act atomically from my applications point of view.
>
> > update table2, table1
> > SET table2.value=table2.value-IF(table1.condition=7,1,0),
> > tab
[snip]
Although my question goes in that direction, it is more about why this
statement
does not act atomically from my applications point of view.
> update table2, table1
> SET table2.value=table2.value-IF(table1.condition=7,1,0),
> table1.condition=8
> WHERE table1.ID = table2.ID_table1
[/snip]
credible information to the contrary, I will stick with the
hypothesis that this is a BUG in the execution engine.
[/snip]
Shawn, what makes an InnoDB table different from any other type of table
with regards to the way UPDATE is processed? When you issue a
multi-table update you must adhere to the
e case when foreign key constraints may temporably be broken
during a multi table update, so that the InnoDB engine does not perform the
query.
Although my question goes in that direction, it is more about why this statement
does not act atomically from my applications point of view.
> update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/28/2004
11:38:58 AM:
> [snip]
> And the query:
>
> UPDATE table1
> INNER JOIN table2 ON table2.ID_table1=table1.ID
> SET table2.value=table2.value-IF(table1.condition=7,1,0),
> table1.condition=8
> WHERE table1.ID IN (1,2)
>
> After that the v
My Testing and results at the bottom. Suspect a buggy implementation of
the IF() calculation (see tests and results).
Kai Ruhnau <[EMAIL PROTECTED]> wrote on 12/28/2004 11:06:43
AM:
> Zitat von [EMAIL PROTECTED]:
>
> > Can you provide us a test case (sample table definitions and sample
data)
[snip]
And the query:
UPDATE table1
INNER JOIN table2 ON table2.ID_table1=table1.ID
SET table2.value=table2.value-IF(table1.condition=7,1,0),
table1.condition=8
WHERE table1.ID IN (1,2)
After that the value's in table2 are still 5 and 2. But I would expect
the 5 to
be a 4.
[/snip]
It was con
Zitat von [EMAIL PROTECTED]:
> Can you provide us a test case (sample table definitions and sample data)
> ?
Sure.
--
CREATE TABLE table1 (
ID int(10) unsigned NOT NULL default '0',
condition int(10) unsigned NOT NULL default '0'
) TYPE=InnoDB;
INSERT INTO table1 VALUES (1,7);
INSERT INTO
[snip]
I believe your answer was neither on topic nor very useful. That is an
excellent warning you quoted from the manual but I do not believe that
it applies to this particular problem. I also humbly disagree with your
analysis of the warning as neither triggers nor stored procedures are in
pro
Can you provide us a test case (sample table definitions and sample data)
?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Kai Ruhnau <[EMAIL PROTECTED]> wrote on 12/28/2004 10:23:23
AM:
> Zitat von [EMAIL PROTECTED]:
>
> > One reason may be due to your ON condition.
> >
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/28/2004
10:21:43 AM:
> [snip]
> UPDATE table1
> LEFT JOIN table2 ON table2.ID=table2.ID_table1
> SET table2.value=table2.value-IF(table1.condition=7,1,0),
> table1.condition=8
> WHERE table1.ID=$id
>
> Is this behavior correct?
> [/snip]
>
> I
Zitat von [EMAIL PROTECTED]:
> One reason may be due to your ON condition.
>
> ON table2.ID=table2.ID_table1
>
> You reference table 2 twice and I doubt that on the table2 table id equals
> id_table1 very often. Because table2 is LEFT JOINed to table1, you will
> only be able to update the rows th
[snip]
UPDATE table1
LEFT JOIN table2 ON table2.ID=table2.ID_table1
SET table2.value=table2.value-IF(table1.condition=7,1,0),
table1.condition=8
WHERE table1.ID=$id
Is this behavior correct?
[/snip]
In the sense that it did what was given to it in the query, yes. From
http://www.mysql.com/upd
application-level, but up to now I
consindered a multi table update an atomic operation.
Is this behavior correct?
Greetings
Kai
--
This signature is left as an exercise for the reader.
Unsatz des Jahres:
$POLITIKER ruft $PARTEI zur Geschlossenheit.
--
MySQL General Mailing List
For list archives: http
On 22-Jan-2004 Michael McTernan wrote:
> Hi there,
>
> Thanks for your quick response!
>
>> Why all the locks, temp tables and updates? You can just do:
>>
>> SELECT
>> CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX
>> FROM
>> A LEFT JOIN B ON A.x = B.x
>
> Spot on - many thanks! I wa
ion, but I've
certainly learnt something now :)
Thanks,
Mike
> -Original Message-
> From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
> Sent: 22 January 2004 16:38
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: LOCK TABLES and multi table UPDATE
>
&
Michael McTernan said:
>
> I'm using MySQL 4.0.15-standard-log, on RedHat 9.0 with InnoDb tables.
>
> Essentially I have two tables, one table is a list of integers,
> while the second is a table of integer pairs. e.g.
>
> +---+ +-+-+
> | A | | B.x | B.y |
> +---+ +-+-+
> | 1
Hi there,
I've got a small issue which looks a little like a bug. I'm using MySQL
4.0.15-standard-log, on RedHat 9.0 with InnoDb tables.
Essentially I have two tables, one table is a list of integers, while the
second is a table of integer pairs. e.g.
+---+ +-+-+
| A | | B.x | B.y |
"Mike Brum" <[EMAIL PROTECTED]> wrote:
> Hopefully someone can help me out with this -
>
> I have two tables in mySQL: comments and diary
>
> I want to populate a new field in comments with the current values of diary
> - but only if they meet certain criteria.
>
> Now, the problem is this - my
Hopefully someone can help me out with this -
I have two tables in mySQL: comments and diary
I want to populate a new field in comments with the current values of diary
- but only if they meet certain criteria.
Now, the problem is this - my webserver is using an older version of mySQL
that does
the replicated tables in
a multi-table update statement like this:
update non_replicated_db.table, replicated_db.table
set non_replicated_db.table.val=replicated_db.table.val
where
non_replicated_db.table.val_id=replicated_db.table.val_id;
So this query includes the replicated_db in the update stat
Scott Wong writes:
>
> Version: Mysql 4.0.10-gamma
>
> Description: Mysql hangs and possibly can not recover from a query on an innodb
> table.
>
> How to Repeat :
>
> drop table if exists parent;
> drop table if exists child;
>
> CREATE TABLE parent(id INT NOT NULL,
> PRIMARY KEY (
Scott Wong writes:
>
> Hi, I havent received any response on this so i'll send this again.
> I did check again with 4.0.11-gamma and the problem is still there.
>
>
> Regards,
> Scott
>
Hi!
This will require a major re-write in multi table update's and will
not come up very soon. But should co
Hi, I havent received any response on this so i'll send this again.
I did check again with 4.0.11-gamma and the problem is still there.
Regards,
Scott
> Version: Mysql 4.0.10-gamma
>
> Description: Mysql hangs and possibly can not recover from a query on an innodb
> table.
>
> How to Re
Version: Mysql 4.0.10-gamma
Description: Mysql hangs and possibly can not recover from a query on an innodb
table.
How to Repeat :
drop table if exists parent;
drop table if exists child;
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT
Hi,
I m using MySQL 4.0.2 which now supports multi-table
update query
I am getting error while throwing multi-table UPDATE
query, I don't know what the problem is
To generate an error run below query, it will create a
2 InnoDB table and inserts a dummy record in it
CREATE TABLE `systemde
Hi,
I m using MySQL 4.0.2 which now supports multi-table
update query
I am getting error while throwing multi-table UPDATE
query, I don't know what the problem is
To generate an error run below query, it will create a
2 InnoDB table and inserts a dummy record in it
CREATE TABLE `systemde
bin,
Tuesday, April 16, 2002, 12:40:56 AM, you wrote:
bc> does mysql latest version support multi tables update?
bc> Thanks a lot
Nope. It will come later ...
Look at:
http://www.mysql.com/doc/T/O/TODO_future.html
--
For technical support contracts, goto https://order.mysql.com/
This
Hi, everyone,
does mysql latest version support multi tables update?
Thanks a lot
__
Find, Connect Date! http://personals.yahoo.ca
-
Before posting, please ch
Corey Kaye writes:
> I'm wondering what is the best way to update one table based on another. Can joins
>be performed for an update?
>
> update table1 set table1.field2 = table2.field2 where table1.field1 = table2.field1;
>
> The alternative seams to be:
> select table2.field2 from table2;
> u
I'm wondering what is the best way to update one table based on another. Can joins be
performed for an update?
update table1 set table1.field2 = table2.field2 where table1.field1 = table2.field1;
The alternative seams to be:
select table2.field2 from table2;
update table2 set table2.field2 = $
Hi list:
How can I update a table field from a another table field, the
multitable update is not implemented or yes?.
I am thinking in use a foreign key (references) or tmp table or can I
use table aliases? something like this:
replace into table master( fielda, fieldb , fieldc) select t1.field
47 matches
Mail list logo
