Re: perplexing SQL Update question

What about table2? Can it be that bu is of CHAR data type in table2? Also, I have the feeling that you may not be doing this efficiently. If you are merging data from one table to another, look into the 'MERGE' command.

• Jusung Yang

wassup_2002_2002_at_yahoo.com (wassup) wrote in message news:<1bd2d838.0305230045.77db5b93_at_posting.google.com>...
> 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 Sat May 24 2003 - 00:04:33 CEST