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.
Problem in updating records
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
Problem in updating records
Shivaranjani
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
Re: Problem in updating records
Am i not clear enough
![Question :?:](./images/smilies/icon_question.gif)
G SHIVARANJANI wrote: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.
Shivaranjani
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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)
)
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)
)
bucks wrote:Just a clarification needed,
can we set street=orchestrate.street while placing a same constraint in a where clause?
Shivaranjani
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.
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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
)
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
)
Maveric wrote: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.
Shivaranjani
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Problem in updating records
No, you're just impatient. Your two posts were only an hour apart - for me they came in at 2am and 3am.G SHIVARANJANI wrote:Am i not clear enough![]()
If you want me to respond at that kind of hour it will cost you MEGABUCKS!!!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.