Update statement ignoring 'where' clause

[frenzic]

Hi

I am a DBA - just been in the job two weeks and I would be grateful for some help.

I am trying to update a table with values from another table.

I have spent two days on this and I think I may have blown a fuse in my brain!

The 'from' table has 3 rows in it and the 'to' table has 3094.

I had problems importing the data in date format from a CSV file, so had to create the 'from' table with the date column as char, otherwise it wouldn't import the date.

There are definitely matching serial numbers in both tables and the from table definitely has a date value in DD/MM/RRRR format in the char column pertaining to the date.

The column in the 'to' table is date format.

This is my script:

update <schema_name>.< totable> s1
set <datecolumn>=
(select TO_DATE(<charco lumn>, 'DD/MM/RRRR')
from fromtable s2
where s1.SERIALNUMBER = s2.SERIALNUMBER );

It says it has updated 3094 rows but when I look at the date column, it has set all the rows to null, includign the 3 I am expecting it to update.

[frenzic]

Sadly replying to my own post!

Forget the to_date gubbins - it converts it from char automatically with:

update totable a
set (datecolumn) =
(SELECT datecolumn
FROM fromtable B
WHERE A.serialnumber = B.serialnumber) ;

[debasisdas]

and most saldy that is from a DBA.

[frenzic]

Ahem! ;-) You were up at 20 to 6 on a Sunday morning doing this and you call me sad!! I did say I'd only been in the job 2 weeks! ;-p

[debasisdas]

No not exactly

that was July 28th, 2007,Saturday , 11:12 AM in my time zone.

[frenzic]

No not exactly

that was July 28th, 2007,Saturday , 11:12 AM in my time zone.

Ah that's OK then ;-) OK - I appreciate your efforts, really I do, but I still have the problem, in that it should only update 3 rows and is setting the date in all 3097 rows to null. What am I doing wrong?

[debasisdas]

But you told that is solved , right ......?

[frenzic]

No it isn't - I thought you might have misunderstood. I just found that the to_date bit wasn't needed. It still sets 3097 date fields to null where it should be updating only three to values in the other table

I think I need another where clause outside the brackets, like where exists a.serialnumber = b.serialnumber or something? Is that where I'm going wrong?

This is my latest script:

update totable a
set (datefield) =
(SELECT datefield
FROM fromtable B
WHERE A.serialnumber = B.serialnumber)
WHERE exists A.serialnumber = B.serialnumber

In the log, it says:

Data in file has same endianness as the platform - is this a clue?

[debasisdas]

please post both of your tables structure for my reference.