[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
Jay Blanchard schrieb:
[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 bef
[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]
[snip]
I would be deliriously happy if someone could explain WHY these
statement _should_ have different results. If I am wrong and these
UPDATE statements are supposed to provide different results, I would
like to learn how I became so confused over something this fundamental.
Until I get credible
Zitat von Jay Blanchard
> [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
"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
"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
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
30 matches
Mail list logo
