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? 5 4174
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Fraser Hanson |
last post by:
Hello,
I have a table which has a foreign key relationship with itself. I
want and expect my updates to cascade (deletes definitely cascade as
expected) but instead I just get error 1217:...
|
by: Pekka Henttonen |
last post by:
For some reason SQL0407N error messages never reveal the name of the column
to which a NULL value has been assigned. Here is an example:
SQL0407N Assignment of a NULL value to a NOT NULL column...
|
by: helmut |
last post by:
Hi,
when binding V2 or V6 bind files to a V7 database, we get a SQL0407N
on a system table (SYSIBM.SYSPLANDEP).
The Database versions are:
- V7.2, FP12 WR21336 on the server
- V2.1 or V6.1...
|
by: Solution Seeker |
last post by:
Hi,
We are using vb.net for creating a Windows Application. We are using layered
approach, with a UI layer, Logic, Db Access layer etc.
we are encountering a peculiar problem when we try to...
|
by: PAUL |
last post by:
Hello,
I have 2 datasets I am trying to update. The parent table seems to
update fine but when I go update the chiled table I get an error message
that says I need a related record in the parent...
|
by: PAUL |
last post by:
Hello,
I have 2 tables with a relationship set up in the dataset with vb
..net. I add a new record to the parent table then edit an existing child
record to have the new parent ID. However when I...
|
by: Sergey Kashyrin |
last post by:
Hi,
I'm accessing DB2 v7 on z/OS from Java running on NT (Database server
= DB2 OS/390 7.1.1)
Tried both JDBC type 4 and type 2.
I'm getting regularly SQL0407N errors like this:
...
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for...
|
by: modernshoggoth |
last post by:
G'day all,
I'm trying to update a single table (containing plans for flights) with information from the same and other tables (containing info for organisations and locations).
tblFlightPlans...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |