By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,021 Members | 912 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,021 IT Pros & Developers. It's quick & easy.

Update Fails with SQL0407N

P: n/a
The following Update fails with SQL0407N on Colum "Name", although the
source-table t_Addresses_2005 is defined with NOT NULL on that column:

UPDATE
Addresses.t_Paddresses Old
SET
(Name, Prename, Street, Postcode, Location, Phone, Ad, Fax, Category,
Additional, EMail, Homepage)
=
(SELECT
New.Name, New.Prename, New.Street, New.Postcode, New.Location,
New.Phone, New.Ad, New.Fax, New.Category, New.Additional, New.EMail,
New.Homepage
FROM
Addresses.t_Addresses_2005 New
WHERE
Old.Phone = New.OldPhone
AND
New.Prename IS NOT NULL
)
;

Anyone any idea what could be wrong with my code?

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Janick Bernet wrote:
The following Update fails with SQL0407N on Colum "Name", although the
source-table t_Addresses_2005 is defined with NOT NULL on that column:

UPDATE
Addresses.t_Paddresses Old
SET
(Name, Prename, Street, Postcode, Location, Phone, Ad, Fax, Category,
Additional, EMail, Homepage)
=
(SELECT
New.Name, New.Prename, New.Street, New.Postcode, New.Location,
New.Phone, New.Ad, New.Fax, New.Category, New.Additional, New.EMail,
New.Homepage
FROM
Addresses.t_Addresses_2005 New
WHERE
Old.Phone = New.OldPhone
AND
New.Prename IS NOT NULL
)
;

Anyone any idea what could be wrong with my code?


Does the subselect actually have any qualifying rows? If it doesn't, then
it will use a row of 12 NULLs instead and that would raise the error. You
might want to add a WHERE clause to the UPDATE statement to not update the
row in this situation.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena
Nov 12 '05 #2

P: n/a
Janick Bernet wrote:
The following Update fails with SQL0407N on Colum "Name", although the
source-table t_Addresses_2005 is defined with NOT NULL on that column:

UPDATE
Addresses.t_Paddresses Old
SET
(Name, Prename, Street, Postcode, Location, Phone, Ad, Fax, Category,
Additional, EMail, Homepage)
=
(SELECT
New.Name, New.Prename, New.Street, New.Postcode, New.Location,
New.Phone, New.Ad, New.Fax, New.Category, New.Additional, New.EMail,
New.Homepage
FROM
Addresses.t_Addresses_2005 New
WHERE
Old.Phone = New.OldPhone
AND
New.Prename IS NOT NULL
)
;

Anyone any idea what could be wrong with my code?

Which version of DB2 are you on.
DB2 for LUW V8.1.2 and higher you can use:
MERGE INTO Addresses.t_Paddresses Old
USING Addresses.t_Addresses_2005 New
ON Old.Phone = New.OldPhone
AND New.Prename IS NOT NULL
WHEN MATCHED THEN UPDATE
SET (Name, Prename, Street, Postcode, Location, Phone, Ad, Fax,
Category, Additional, EMail, Homepage)
= (New.Name, New.Prename, New.Street, New.Postcode, New.Location,
New.Phone, New.Ad, New.Fax, New.Category, New.Additional,
New.EMail, New.Homepage);

MERGE is easier to grasp than the equivalent UDDATE....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a

Serge Rielau schrieb:
Janick Bernet wrote:
The following Update fails with SQL0407N on Colum "Name", although the
source-table t_Addresses_2005 is defined with NOT NULL on that column:

UPDATE
Addresses.t_Paddresses Old
SET
(Name, Prename, Street, Postcode, Location, Phone, Ad, Fax, Category,
Additional, EMail, Homepage)
=
(SELECT
New.Name, New.Prename, New.Street, New.Postcode, New.Location,
New.Phone, New.Ad, New.Fax, New.Category, New.Additional, New.EMail,
New.Homepage
FROM
Addresses.t_Addresses_2005 New
WHERE
Old.Phone = New.OldPhone
AND
New.Prename IS NOT NULL
)
;

Anyone any idea what could be wrong with my code?

Which version of DB2 are you on.
DB2 for LUW V8.1.2 and higher you can use:
MERGE INTO Addresses.t_Paddresses Old
USING Addresses.t_Addresses_2005 New
ON Old.Phone = New.OldPhone
AND New.Prename IS NOT NULL
WHEN MATCHED THEN UPDATE
SET (Name, Prename, Street, Postcode, Location, Phone, Ad, Fax,
Category, Additional, EMail, Homepage)
= (New.Name, New.Prename, New.Street, New.Postcode, New.Location,
New.Phone, New.Ad, New.Fax, New.Category, New.Additional,
New.EMail, New.Homepage);

MERGE is easier to grasp than the equivalent UDDATE....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Nov 12 '05 #4

P: n/a
Hi Serge

Thanks, Serge, that looks much nicer. But I don't really understand,
why IBM had to introduce a new keyword for something which could have
been implemented as an extension of UPDATE (as it is done in other
DB's). Is MERGE standard SQL?

Regards
Janick Bernet

Nov 12 '05 #5

P: n/a
Janick Bernet wrote:
Hi Serge

Thanks, Serge, that looks much nicer. But I don't really understand,
why IBM had to introduce a new keyword for something which could have
been implemented as an extension of UPDATE (as it is done in other
DB's). Is MERGE standard SQL?

There are a couple of problems with UPDATE FROM.
First of all at least in SQL Server the feature is not deterministic if
the join isn't row preserving on the UPDATE target.
Secondly UPDATE FROM is only part of the solution.
Often UPDATE FROM goes hand in hand with an INSERT
"If the row exists update, otherwise insert" .. (and if the update
results in a 0 maybe delete)..
MERGE addresses the whole problem in one shot since you can specify
UPDATE, DELETE and INSERT branches. MERGE also clearly defines what
happens if two rows in the source match the same target row.

MERGE is part of the SQL Standard and is supported today by Oracle 9i
and later as well as DB2 for LUW V8.1.2 and later.
The other IBM products will support MERGE in the future.

Sometimes its appropriate to extend existing language, sometimes it's
just better to start from scratch...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.