perplexing SQL Update question

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 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!! Received on Fri May 23 2003 - 10:45:53 CEST