| perplexing question [message #7140] |
Fri, 23 May 2003 02:59  |
wassup
Messages: 3
Registered: May 2003
|
Junior Member |
|
|
I have an Update SQL using a correlated subquery that just doesn't seem to work when it should...
Here are the 'key' players...
field type
bu varchar2(5)
book varchar2(10)
dt_time date
seq integer
posted_flag varchar2(1)
These are the keys (uniq) in table1 and table2
I am moving data from table1 to table2 and after moving I need to set the posted_flag to 'Y' in table1 so here goes my Update SQL.
This *has* to BE ANSI nothing specific to Oracle here
Line 1 : Update table1
Line 2 : set posted_flag = 'Y'
Line 3 : where exists (select 'x' from table2
Line 4 : where table1.bu = table2.bu
Line 5 : and table1.book = table2.book
Line 6 : and table1.dt_time = table2.dt_time
Line 7 : and table1.seq = table2.seq)
Line 8 : and bu = 'CHILD'
Line 9 : and book = 'MAIN'
Line 10: and posted_flag = 'N'
For some stupid reason this SQL does not work . However it works if I comment out Line 8 or replace it with
Line 8 : and bu like 'CHILD%'
So this would point to additional characters in field bu in table1, but I did a dump of table1 like so
SQL > Select bu, dump(bu) from table1
and I get the query coming back with
bu dump
___ _____
CHILD type 1 and the ASCII codes for C H I L D
and if I do a
SQL > Select bu||'$' from table1
I see the result as
CHILD$
don't seem like there are any additional characters at the end so why does this update not work??????
Any help much appreciated!!
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
