Problem in updating records

[G SHIVARANJANI]

Hi,

I have a source which has nullable fields . and these nullable fields are being used to update a record in the table.

Like paon,saon,street etc.. these are nullable and the query used for update is:

UPDATE ()
UPDATE
LAND_REGISTRY
SET
(Trim(STREET)= TRIM(ORCHESTRATE.Street))
WHERE
(Trim(Paon) = TRIM(ORCHESTRATE.Paon) AND
Trim(SAON) = TRIM(ORCHESTRATE.Saon) AND
Trim(STREET)= TRIM(ORCHESTRATE.Street)
)

while loading the records in the table in first attempt,the record with null field is getting updated ,

but when i am trying to reload the record instead of getting updated the record is getting inserted.

[G SHIVARANJANI]

Am i not clear enough

Hi,

I have a source which has nullable fields . and these nullable fields are being used to update a record in the table.

Like paon,saon,street etc.. these are nullable and the query used for update is:

UPDATE ()
UPDATE
LAND_REGISTRY
SET
(Trim(STREET)= TRIM(ORCHESTRATE.Street))
WHERE
(Trim(Paon) = TRIM(ORCHESTRATE.Paon) AND
Trim(SAON) = TRIM(ORCHESTRATE.Saon) AND
Trim(STREET)= TRIM(ORCHESTRATE.Street)
)

while loading the records in the table in first attempt,the record with null field is getting updated ,

but when i am trying to reload the record instead of getting updated the record is getting inserted.

[bucks]

Just a clarification needed,

can we set street=orchestrate.street while placing a same constraint in a where clause?

[G SHIVARANJANI]

Actually no necessary....
as we are updating the fiels...
let me correct it..

UPDATE
LAND_REGISTRY
SET
(Trim(Land_registry_id)= TRIM(ORCHESTRATE.Land_registry_id))
WHERE
(Trim(Paon) = TRIM(ORCHESTRATE.Paon) AND
Trim(SAON) = TRIM(ORCHESTRATE.Saon) AND
Trim(STREET)= TRIM(ORCHESTRATE.Street)
)

Just a clarification needed,

can we set street=orchestrate.street while placing a same constraint in a where clause?

[Maveric]

AS far as i know you cannot compare two NULLs. The where clause will always return falls and the record wont update. Also the trim function wont work for a Nullable field. Try converting the NULLs to blank and try. You will probably have to wait for the gurus to answer and see if there is a work around.

[G SHIVARANJANI]

Hi,

Using the Query this way solves the problem:


UPDATE
LAND_REGISTRY
SET
(Trim(Land_registry_id)= TRIM(ORCHESTRATE.Land_registry_id))
WHERE
(NVL(Trim(Paon),'N/A') = NVL(TRIM(ORCHESTRATE.Paon),'N/A') AND
)

AS far as i know you cannot compare two NULLs. The where clause will always return falls and the record wont update. Also the trim function wont work for a Nullable field. Try converting the NULLs to blank and try. You will probably have to wait for the gurus to answer and see if there is a work around.

[ray.wurlod]

Am i not clear enough

No, you're just impatient. Your two posts were only an hour apart - for me they came in at 2am and 3am.

If you want me to respond at that kind of hour it will cost you MEGABUCKS!!!