|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
January 3rd, 2006, 03:05 PM
|
|||
|
|||
|
Update Bug Or Me?
I just ran the following update SQL in Firebird 1.5 which should have swapped the values of two columns according to SQL set theory as I understand it. Instead, I ended up with the same value in each column.
UPDATE MYTABLE SET FIELD1 = FIELD2, FIELD2 = FIELD1; Unlike a procedural language, this is suppose to work. Certainly according to my SQL Guru Joe Celko. His book "SQL for Smarties" specifically states(page. 123): ========== Each assignment in the <set clause list> is executed in parallel and each SET clause changes all the qualified rows at once. .... This feature lets you write a statement that will swap the values in two columns thus: UPDATE MyTable SET a=b,b=a; =========== Can anyone enlighten me? Thanks, Clive
|
|
#2
January 4th, 2006, 02:30 AM
|
||||
|
||||
|
Better call Joe for help, here is MySQL:
Code:
mysql> insert into pippo(field1, field2) values ('a', 'b');
Query OK, 1 row affected (0.19 sec)
mysql> select * from pippo;
+--------+--------+
| field1 | field2 |
+--------+--------+
| a | b |
+--------+--------+
1 row in set (0.05 sec)
mysql> update pippo set field1 = field2, field2 = field1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pippo;
+--------+--------+
| field1 | field2 |
+--------+--------+
| b | b |
+--------+--------+
1 row in set (0.00 sec)
mysql> select version();
+---------------+
| version() |
+---------------+
| 5.0.17-nt-max |
+---------------+
1 row in set (0.01 sec)
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias)
|
|
#3
January 4th, 2006, 02:34 AM
|
||||
|
||||
|
Ok, add another one for PostgreSQL (v 8.1) it works as expected, Joe would be happy!!
You can draw your conclusions on which database to choose
|
|
#4
January 4th, 2006, 01:34 PM
|
|||
|
|||
|
Oracle (of course ;> ) works as defined by Joe.
I find it much more troubling that different databases give different results for the same SQL than that some databases do not support features. Now I am wondering what other areas of Firebird (and MySQL) provide unexpected results. As the schemas I design are well normalized, my display and reporting SQL is often very complex.
|
|
#5
January 4th, 2006, 04:15 PM
|
|||
|
|||
|
Just want to add to the list
 It works as expected in HSQLDB, Derby, FirstSQL, SQL Server and SQL Anywhere. It is not working with McKoi and Firebird 2.0 (Alpha) as well. I posted a message to the Firebird mailing list, and will post the answer here. Regards Thomas
|
|
#6
January 5th, 2006, 04:01 AM
|
|||
|
|||
|
OK, here is the answer I got from the Firebird mailing list. Seems that this is known, and won't change in the future.
Ann is one of the major leads in the development of Firebird: Quote:
|
|
#7
January 5th, 2006, 04:06 PM
|
|||
|
|||
|
Quote:
This is, of course, troubling and IMO needs to be prominently documented. Meanwhile, I am very grateful to you and Ann for documenting how Firebird does treat updates so that we can avoid unwelcome surprises in other UPDATE SQL. Clive
|
|
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Update Bug Or Me? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
