Any idea about order in which values are set in an update query
e.g. field1. = value1 , field2 = value
now if field1 is set first and then field2 (logically yes
but I have seen varying patterns

Nope, I have only seen it from left to right. Can you show a repro of
otherwise.


--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



[quoted text, click to view]

create table #tmp(i int, j int)
insert #tmp select 1,1
union all select 2,2
go
declare @i int
set @i=1
update #tmp
set @i=i=@i+1,
@i=j=@i+1
go
select * from #tmp
go
drop table #tmp
go

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



[quoted text, click to view]

Oops, I replied to your other post. Here it is again...

create table #tmp(i int, j int)
insert #tmp select 1,1
union all select 2,2
go
declare @i int
set @i=1
update #tmp
set @i=i=@i+1,
@i=j=@i+1
go
select * from #tmp
go
drop table #tmp
go

If everything is done *simultaneously* we should not get the *rolling*
effect. If this makes any clearer.

create table #tmp(i int, j int)
insert #tmp select 1,1
union all select 2,2
go
declare @i int
set @i=1
update #tmp
set @i=i=@i+1,
j=@i+1
go
select * from #tmp
go
drop table #tmp
go

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



[quoted text, click to view]

heheheheh...I have learned that I should never say never on here. Some
*nuts* out there are going to prove it otherwise. I'm thoroughly humbled by
these *nuts* and still have much to learn from them. Boyz, I'm still a long
way from learning a 10th of what they know. :(:)

Cheers,
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



[quoted text, click to view]

In logical terms the values are assigned to all columns *simultaneously* in
an UPDATE query. If a column being updated is referred to on the right hand
side of the assignment operator (=) then its value is always the value
*before* the update happened. The only way to break this that I'm aware of
is by assigning a non-deterministic UDF or the NEWID() function to a
column - then, inevitably, the function will be called twice and two
different values will be assigned. But if you're using non-deterministic
functions then presumably you don't care what value is assigned anyway.

[quoted text, click to view]

Can you give an example of what you mean by this? There should be no
variation from the standard behaviour.

--
David Portas
------------
Please reply only to the newsgroup
--

[quoted text, click to view]

Really? Can you give an example of that?

--
David Portas
------------
Please reply only to the newsgroup
--

Thanks OJ. That's another exception - if you use a variable on both sides of
an assignment. But it doesn't seem to relate to the example pseudo-code that
the OP gave (another way of saying "I just didn't think of that" ;-) )

--
David Portas
------------
Please reply only to the newsgroup
--

btw, you don't need to use a var on both sides of the assignment. this
example still shows the *rolling* effect.

create table #tmp(i int, j int)
insert #tmp select 1,1
union all select 2,2
go
declare @i int
set @i=1
update #tmp
set @i=i=i+1,
j=@i+1
go
select * from #tmp
go
drop table #tmp
go

cheers,
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



[quoted text, click to view]

Of course this is only good with t-sql update extension. We're working with
MS SQL Server, are we not. ;~)

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



[quoted text, click to view]

Only if you are using proprietary stuff which depends on the underlying
file system. Here is how it works in Standard SQL-92.

There is no FROM clause in a Standard SQL UPDATE statement; it would
make no sense. Other products (SQL Server, Sybase and Ingres) also use
the UPDATE .. FROM syntax, but with different semantics. So it does not
port, or even worse, when you do move it, it trashes your database.
Other programmers cannot read it and maintaining it is harder. And when
Microsoft decides to change it, you will have to do a re-write.
Remember the deprecated "*=" versus "LEFT OUTER JOIN" conversions?

The correct syntax for a searched update statement is

<update statement> ::=
UPDATE <table name>
SET <set clause list>
[WHERE <search condition>]

<set clause list> ::=
<set clause> [{ , <set clause> }...]

<set clause> ::= <object column> = <update source>

<update source> ::= <value expression> | NULL | DEFAULT

<object column> ::= <column name>

The UPDATE clause simply gives the name of the base table or updatable
view to be changed.

Notice that no correlation name is allowed in the UPDATE clause; this is
to avoid some self-referencing problems that could occur. But it also
follows the data model in Standard SQL. When you give a table expression
a correlation name, it is to act as if a materialized table with that
correlation name has been created in the database. That table then is
dropped at the end of the statement. If you allowed correlation names
in the UPDATE clause, you would be updating the materialized table,
which would then disappear and leave the base table untouched.

The SET clause is a list of columns to be changed or made; the WHERE
clause tells the statement which rows to use. For this discussion, we
will assume the user doing the update has applicable UPDATE privileges
for each <object column>.

* The WHERE Clause

As mentioned, the most important thing to remember about the WHERE
clause is that it is optional. If there is no WHERE clause, all rows in
the table are changed. This is a common error; if you make it,
immediately execute a ROLLBACK statement.

All rows that test TRUE for the <search condition> are marked as a
subset and not as individual rows. It is also possible that this subset
will be empty. This subset is used to construct a new set of rows that
will be inserted into the table when the subset is deleted from the
table. Note that the empty subset is a valid update that will fire
declarative referential actions and triggers.

* The SET Clause

Each assignment in the <set clause list> is executed in parallel and
each SET clause changes all the qualified rows at once. Or at least
that is the theoretical model. In practice, implementations will first
mark all of the qualified rows in the table in one pass, using the WHERE
clause. If there were no problems, then the SQL engine makes a copy of
each marked row in working storage. Each SET clause is executed based
on the old row image and the results are put in the new row image.
Finally, the old rows are deleted and the new rows are inserted. If an
error occurs during all of this, then system does a ROLLBACK, the table
is left unchanged and the errors are reported. This parallelism is not
like what you find in a traditional third-generation programming
language, so it may be hard to learn. This feature lets you write a
statement that will swap the values in two columns, thus:

UPDATE MyTable
SET a = b, b = a;

This is not the same thing as

BEGIN ATOMIC
UPDATE MyTable
SET a = b;
UPDATE MyTable
SET b = a;
END;

In the first UPDATE, columns a and b will swap values in each row. In
the second pair of UPDATEs, column a will get all of the values of
column b in each row. In the second UPDATE of the pair, a, which now
has the same value as the original value of b, will be written back into
column b -- no change at all. There are some limits as to what the
value expression can be. The same column cannot appear more than once
in a <set clause list> -- which makes sense, given the parallel nature
of the statement. Since both go into effect at the same time, you would
not know which SET clause to use.

If a subquery expression is used in a <set clause>, and it returns a
single value, the result set is cast to a scalar; if it returns an
empty, the result set is cast to a NULL; if it returns multiple rows, a
cardinality violation is raised.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

oj,

I think it shows this "rolling" only with t-SQL update extension where an
intermediate variable is involved, since it does procedural processing for
variable assignment. In the case of OP, being a direct update, it should be
simultaneous. Otherwise the following UPDATE statement shouldn't swap the
values, right?

UPDATE #tmp
SET i = j, j = i ;

--
- Anith
( Please reply to newsgroups only )

:-) *nuts*

--
- Anith
( Please reply to newsgroups only )