Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 7 of 7 total |
update statement (invalid?) |
Thu, Apr 5 2007 10:58 AM | Permanent Link |
"Harry de Boer" | 1.02
update tbl set veld2 = veld2 + 'a' where veld1 = 1 //veld 2 is a char(10) field the query plan tells me that one row is updated, but that's not right, the row is unchanged! ...either the statment is invalid (and an error should be raised), or is it a bug? Regards, Harry ============================================================================ ==== SQL Update (Executed by ElevateDB 1.02 Build 1) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ============================================================================ ==== UPDATE "tbl" SET "veld2" = "veld2" + 'a' WHERE "veld1" = 1 Target Table ------------ tbl: 1011204 rows Filtering --------- The following filter condition was applied to the tbl table: "veld1" = 1 [Index scan: 1 keys, 4096 bytes estimated cost] ============================================================================ ==== 1 row(s) updated in 0,015 secs ============================================================================ ==== |
Thu, Apr 5 2007 11:04 AM | Permanent Link |
"Harry de Boer" | the same happens with update tbl set veld2 = veld2 || 'a' where veld1 = 1
//instead + here the || is used. The query plan shows exactly the same (so with +) but the row did not change at all Regards, Harry |
Thu, Apr 5 2007 12:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Harry,
<< update tbl set veld2 = veld2 + 'a' where veld1 = 1 //veld 2 is a char(10) field the query plan tells me that one row is updated, but that's not right, the row is unchanged! ..either the statment is invalid (and an error should be raised), or is it a bug? >> CHAR columns are fixed-length columns, meaning that they are padded to the length of the column with spaces. To append a string to the end of an existing value, you'll need to trim the column value like this: update tbl set veld2 = RTRIM(veld2) + 'a' where veld1 = 1 -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 5 2007 1:16 PM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< CHAR columns are fixed-length columns, meaning that they are padded to the length of the column with spaces. To append a string to the end of an existing value, you'll need to trim the column value like this: >> This is a good example of the implications/disadvantages of silently truncating data instead of raising a proper exception, such that the user is informed that the assignment source does not fit the target. Ole Willy Tuv |
Thu, Apr 5 2007 2:02 PM | Permanent Link |
"Harry de Boer" | Tim,
I changed the fieldtype to VARCHAR(10) but that doesn't change it. You still can't use "update tbl set veld2 = veld2 + 'a' where veld1 = 1". My guess was that varchar isn't fixed so it should work. Is this proper behavior and should one always use TRIM functions. Also, the lack of an error (or warning) here is not the nicest way of handling this I think (set me on the wrong foot -still limping). Still, what I have been testing now is keeping me very happy so far. ElevateDB is very fast and has lots of functionality (we are still -but very satisfied- 'on' dbIsam 3.3). Pitty we have to wait till summer to see SQL scripting again, but as I read there is lots of new things to come there too, so the expectations are high Regards, Harry "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht news:DAEDCE53-FF97-4759-9223-EB03C1EF78E3@news.elevatesoft.com... > Harry, > > << update tbl set veld2 = veld2 + 'a' where veld1 = 1 //veld 2 is a > char(10) field > > the query plan tells me that one row is updated, but that's not right, the > row is unchanged! ..either the statment is invalid (and an error should be > raised), or is it a bug? >> > > CHAR columns are fixed-length columns, meaning that they are padded to the > length of the column with spaces. To append a string to the end of an > existing value, you'll need to trim the column value like this: > > update tbl set veld2 = RTRIM(veld2) + 'a' where veld1 = 1 > > -- > Tim Young > Elevate Software > www.elevatesoft.com > > |
Fri, Apr 6 2007 3:08 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Harry,
<< I changed the fieldtype to VARCHAR(10) but that doesn't change it. You still can't use "update tbl set veld2 = veld2 + 'a' where veld1 = 1". My guess was that varchar isn't fixed so it should work. >> You changed the column type, but the column data still contains padded data. You'll need to update the data so that it doesn't contain trailing spaces anymore. << Also, the lack of an error (or warning) here is not the nicest way of handling this I think (set me on the wrong foot -still limping). >> It's only an issue with CHAR columns, which should only be used sparingly. << Still, what I have been testing now is keeping me very happy so far. ElevateDB is very fast and has lots of functionality (we are still -but very satisfied- 'on' dbIsam 3.3). Pitty we have to wait till summer to see SQL scripting again, but as I read there is lots of new things to come there too, so the expectations are high >> Good to hear. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Apr 6 2007 3:11 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< This is a good example of the implications/disadvantages of silently truncating data instead of raising a proper exception, such that the user is informed that the assignment source does not fit the target. >> I understand, but again, you're looking at it from the outside and not with any understanding of how EDB is designed. The way it works right now, it is possible that such an exception would be going off like a faulty car alarm on a noisy street during most operations. IOW, it would take some time to put something in that wasn't more trouble than it was worth. All values in EDB descend from the same object(s), so there is a lot of overlap into areas where we don't want such exceptions. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |