Update SQL Query

Raquel Rodriguez

Update SQL Query
Table T1 has columns C1 and C2. Table T2 has columns
C1 and C2. All columns are not-nullable.

For rows in tables T1 and T2 that match on C1 (WHERE
T1.C1 = T2.C1), I want to update T1.C2=T2.C2;
something along similar lines:

UPDATE T1
SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)

The above SQL fails with SQLCODE -104 (Column cannot
contain a NULL value.) which I understand since C2 is
not-nullable.

The following doesn't work either and fails with the
same SQLCODE and message:

UPDATE T1
SET C2 = (SELECT COALESCE(C2,'UNKNOWN') FROM T2 WHERE
T1.C1=T2.C1)

Any ideas as to how I can accomplish the update. The
following works but in my opinion is incredibly
complex considering the simple requirement I have:

UPDATE T1
SET C2 =
(SELECT T2.B FROM
(
SELECT T1.*, VALUE(T2.C2,'UNKNOWN') AS B
FROM T1 LEFT OUTER JOIN T2
ON T1.C1=T2.C1
) T2
WHERE T1.C1 = T2.C1
)
;

There has to be a simple way. Requirement seems so
simple.

TIA
Raquel.

__________________________________
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

teldb2kals

Re: Update SQL Query
(in response to Raquel Rodriguez)
Hi Raquel,

Are u sure about you got -104 ? I would expect to see -407, which is the
sqlcode for 'column cannot contain a null value'.

Obviously there are some C1 values in T1 that dont exist in T2. You could
add a WHERE EXISTS clause to your update.

UPDATE T1
SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)
WHERE EXISTS
(SELECT 1 FROM T2 WHERE T1.C1=T2.C1)

Regards,
Kals.


On Tue, 30 Dec 2003 11:10:54 -0800, Raquel Rodriguez
<[login to unmask email]> wrote:

>Table T1 has columns C1 and C2. Table T2 has columns
>C1 and C2. All columns are not-nullable.
>
>For rows in tables T1 and T2 that match on C1 (WHERE
>T1.C1 = T2.C1), I want to update T1.C2=T2.C2;
>something along similar lines:
>
>UPDATE T1
>SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)
>
>The above SQL fails with SQLCODE -104 (Column cannot
>contain a NULL value.) which I understand since C2 is
>not-nullable.
>
>The following doesn't work either and fails with the
>same SQLCODE and message:
>
>UPDATE T1
>SET C2 = (SELECT COALESCE(C2,'UNKNOWN') FROM T2 WHERE
>T1.C1=T2.C1)
>
>Any ideas as to how I can accomplish the update. The
>following works but in my opinion is incredibly
>complex considering the simple requirement I have:
>
>UPDATE T1
>SET C2 =
>(SELECT T2.B FROM
>(
>SELECT T1.*, VALUE(T2.C2,'UNKNOWN') AS B
>FROM T1 LEFT OUTER JOIN T2
>ON T1.C1=T2.C1
>) T2
>WHERE T1.C1 = T2.C1
>)
>;
>
>There has to be a simple way. Requirement seems so
>simple.
>
>TIA
>Raquel.
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Horacio Villa

Re: Update SQL Query
(in response to teldb2kals)
Raquel,

this works:

UPDATE T1
SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)
WHERE EXISTS (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)

Your T1 table has values that don't exist in T2.

Regards & happy new year,

Horacio Villa




Raquel Rodriguez
<raquel_rodriguezus To: [login to unmask email]
@YAHOO.COM> cc:
Sent by: DB2 Data Subject: Update SQL Query
Base Discussion
List
<[login to unmask email]
G>


12/30/03 04:10 PM
Please respond to
DB2 Database
Discussion list at
IDUG






Table T1 has columns C1 and C2. Table T2 has columns
C1 and C2. All columns are not-nullable.

For rows in tables T1 and T2 that match on C1 (WHERE
T1.C1 = T2.C1), I want to update T1.C2=T2.C2;
something along similar lines:

UPDATE T1
SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)

The above SQL fails with SQLCODE -104 (Column cannot
contain a NULL value.) which I understand since C2 is
not-nullable.

The following doesn't work either and fails with the
same SQLCODE and message:

UPDATE T1
SET C2 = (SELECT COALESCE(C2,'UNKNOWN') FROM T2 WHERE
T1.C1=T2.C1)

Any ideas as to how I can accomplish the update. The
following works but in my opinion is incredibly
complex considering the simple requirement I have:

UPDATE T1
SET C2 =
(SELECT T2.B FROM
(
SELECT T1.*, VALUE(T2.C2,'UNKNOWN') AS B
FROM T1 LEFT OUTER JOIN T2
ON T1.C1=T2.C1
) T2
WHERE T1.C1 = T2.C1
)
;

There has to be a simple way. Requirement seems so
simple.

TIA
Raquel.

__________________________________
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: Update SQL Query
(in response to Horacio Villa)
-104 is a syntax error. If you really got -104, I would try to
use a table alias on the update statement and use this alias in the subquery. I
always do it that way, but I'm not sure if it is only a question of style or a
real requirement.

like this:

update t1 a
set c2 = (select c2
from t2
where c1 = a.c1);

and: I don't think that the subquery gives a NULL value, if no row can
be found. I believe, you will get a very specific error, if the subquery
finds no (or more than one) row. So it should be no problem, if c2 is not
nullable, as long as you don't get a real NULL value from the subquery).

It is never wrong to add a WHERE EXISTS condition to the UPDATE, as
others have suggested. If you can get more than one row in the subquery,
you may have to use max(c2) or anything of this kind in the subquery.

HTH, regards

Bernd



Am Die, 30 Dez 2003 schrieben Sie:
> Table T1 has columns C1 and C2. Table T2 has columns
> C1 and C2. All columns are not-nullable.
>
> For rows in tables T1 and T2 that match on C1 (WHERE
> T1.C1 = T2.C1), I want to update T1.C2=T2.C2;
> something along similar lines:
>
> UPDATE T1
> SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)
>
> The above SQL fails with SQLCODE -104 (Column cannot
> contain a NULL value.) which I understand since C2 is
> not-nullable.
>
> The following doesn't work either and fails with the
> same SQLCODE and message:
>
> UPDATE T1
> SET C2 = (SELECT COALESCE(C2,'UNKNOWN') FROM T2 WHERE
> T1.C1=T2.C1)
>
> Any ideas as to how I can accomplish the update. The
> following works but in my opinion is incredibly
> complex considering the simple requirement I have:
>
> UPDATE T1
> SET C2 =
> (SELECT T2.B FROM
> (
> SELECT T1.*, VALUE(T2.C2,'UNKNOWN') AS B
> FROM T1 LEFT OUTER JOIN T2
> ON T1.C1=T2.C1
> ) T2
> WHERE T1.C1 = T2.C1
> )
> ;
>
> There has to be a simple way. Requirement seems so
> simple.
>
> TIA
> Raquel.
>
> __________________________________
> Do you Yahoo!?
> Find out what made the Top Yahoo! Searches of 2003
> http://search.yahoo.com/top2003
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

teldb2kals

Re: Update SQL Query
(in response to Bernd Oppolzer)
Hi Bernd,

The subquery does seem to return a null if no rows are found. Following is
an extract from the SQL Reference (UPDATE statement) :

"
scalar-fullselect
Specifies a fullselect that returns a single row with a single column. The
column value is assigned to the corresponding column-name. If the
fullselect returns no rows, the null value is assigned; an error occurs if
the column to be updated is not nullable. An error also occurs if there is
more than one row in the result.
"

Using the EXISTS clause will ensure that only the relevant rows will get
searched and updated.

An alias is probably not required ; it will be needed only if we have more
than one reference to the same table. But yes, I like using an alias.

Happy New Year to everyone.

Regards,
Kals


On Wed, 31 Dec 2003 00:20:21 +0100, Bernd Oppolzer <[login to unmask email]
ONLINE.DE> wrote:

>-104 is a syntax error. If you really got -104, I would try to
>use a table alias on the update statement and use this alias in the
subquery. I
>always do it that way, but I'm not sure if it is only a question of style
or a
>real requirement.
>
>like this:
>
>update t1 a
> set c2 = (select c2
> from t2
> where c1 = a.c1);
>
>and: I don't think that the subquery gives a NULL value, if no row can
>be found. I believe, you will get a very specific error, if the subquery
>finds no (or more than one) row. So it should be no problem, if c2 is not
>nullable, as long as you don't get a real NULL value from the subquery).
>
>It is never wrong to add a WHERE EXISTS condition to the UPDATE, as
>others have suggested. If you can get more than one row in the subquery,
>you may have to use max(c2) or anything of this kind in the subquery.
>
>HTH, regards
>
>Bernd
>
>
>
>Am Die, 30 Dez 2003 schrieben Sie:
>> Table T1 has columns C1 and C2. Table T2 has columns
>> C1 and C2. All columns are not-nullable.
>>
>> For rows in tables T1 and T2 that match on C1 (WHERE
>> T1.C1 = T2.C1), I want to update T1.C2=T2.C2;
>> something along similar lines:
>>
>> UPDATE T1
>> SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)
>>
>> The above SQL fails with SQLCODE -104 (Column cannot
>> contain a NULL value.) which I understand since C2 is
>> not-nullable.
>>
>> The following doesn't work either and fails with the
>> same SQLCODE and message:
>>
>> UPDATE T1
>> SET C2 = (SELECT COALESCE(C2,'UNKNOWN') FROM T2 WHERE
>> T1.C1=T2.C1)
>>
>> Any ideas as to how I can accomplish the update. The
>> following works but in my opinion is incredibly
>> complex considering the simple requirement I have:
>>
>> UPDATE T1
>> SET C2 =
>> (SELECT T2.B FROM
>> (
>> SELECT T1.*, VALUE(T2.C2,'UNKNOWN') AS B
>> FROM T1 LEFT OUTER JOIN T2
>> ON T1.C1=T2.C1
>> ) T2
>> WHERE T1.C1 = T2.C1
>> )
>> ;
>>
>> There has to be a simple way. Requirement seems so
>> simple.
>>
>> TIA
>> Raquel.
>>
>> __________________________________

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Raquel Rodriguez

Re: Update SQL Query
(in response to teldb2kals)
Thank you VERY much Kals and Horacio. Your suggested
queries worked like a charm.

Appreciate it.
Raquel.

--- Horacio Villa <[login to unmask email]> wrote:
> Raquel,
>
> this works:
>
> UPDATE T1
> SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)
> WHERE EXISTS (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)
>
> Your T1 table has values that don't exist in T2.
>
> Regards & happy new year,
>
> Horacio Villa
>
>
>
>
> Raquel Rodriguez
> <raquel_rodriguezus To:
> [login to unmask email]
> @YAHOO.COM> cc:
> Sent by: DB2 Data
> Subject: Update SQL Query
> Base Discussion
> List
> <[login to unmask email]
> G>
>
>
> 12/30/03 04:10 PM
> Please respond to
> DB2 Database
> Discussion list at
> IDUG
>
>
>
>
>
>
> Table T1 has columns C1 and C2. Table T2 has columns
> C1 and C2. All columns are not-nullable.
>
> For rows in tables T1 and T2 that match on C1 (WHERE
> T1.C1 = T2.C1), I want to update T1.C2=T2.C2;
> something along similar lines:
>
> UPDATE T1
> SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)
>
> The above SQL fails with SQLCODE -104 (Column
> cannot
> contain a NULL value.) which I understand since C2
> is
> not-nullable.
>
> The following doesn't work either and fails with the
> same SQLCODE and message:
>
> UPDATE T1
> SET C2 = (SELECT COALESCE(C2,'UNKNOWN') FROM T2
> WHERE
> T1.C1=T2.C1)
>
> Any ideas as to how I can accomplish the update. The
> following works but in my opinion is incredibly
> complex considering the simple requirement I have:
>
> UPDATE T1
> SET C2 =
> (SELECT T2.B FROM
> (
> SELECT T1.*, VALUE(T2.C2,'UNKNOWN') AS B
> FROM T1 LEFT OUTER JOIN T2
> ON T1.C1=T2.C1
> ) T2
> WHERE T1.C1 = T2.C1
> )
> ;
>
> There has to be a simple way. Requirement seems so
> simple.
>
> TIA
> Raquel.
>
> __________________________________
> Do you Yahoo!?
> Find out what made the Top Yahoo! Searches of 2003
> http://search.yahoo.com/top2003
>
>
---------------------------------------------------------------------------------
>
> Welcome to the IDUG DB2-L list. To unsubscribe, go
> to the archives and home
> page at
> http://www.idugdb2-l.org/archives/db2-l.html. From
> that page select
> "Join or Leave the list". If you will be out of the
> office, send the SET
> DB2-L NO MAIL command to [login to unmask email]
> The IDUG List Admins
> can be reached at [login to unmask email]
> Find out the latest on
> IDUG conferences at
> http://conferences.idug.org/index.cfm
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go
> to the archives and home page at
> http://www.idugdb2-l.org/archives/db2-l.html. From
> that page select "Join or Leave the list". If you
> will be out of the office, send the SET DB2-L NO
> MAIL command to [login to unmask email] The IDUG
> List Admins can be reached at
> [login to unmask email] Find out the latest
> on IDUG conferences at
http://conferences.idug.org/index.cfm


__________________________________
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: Update SQL Query
(in response to Raquel Rodriguez)
Thank you, I didn't know that.

But then, the error should not have been a -104, and the solution with COALESCE
should have worked, IMO.

No, I see now: the subquery returns NULL, if nothing is found. The COALESCE only
would help, if NULL was found, but not, if no row is found.

Also Happy New Year to everyone. I appreciate the people on this list, I've
learned a lot by participating in this community.

Regards

Bernd



Am Mit, 31 Dez 2003 schrieben Sie:
> Hi Bernd,
>
> The subquery does seem to return a null if no rows are found. Following is
> an extract from the SQL Reference (UPDATE statement) :
>
> "
> scalar-fullselect
> Specifies a fullselect that returns a single row with a single column. The
> column value is assigned to the corresponding column-name. If the
> fullselect returns no rows, the null value is assigned; an error occurs if
> the column to be updated is not nullable. An error also occurs if there is
> more than one row in the result.
> "
>
> Using the EXISTS clause will ensure that only the relevant rows will get
> searched and updated.
>
> An alias is probably not required ; it will be needed only if we have more
> than one reference to the same table. But yes, I like using an alias.
>
> Happy New Year to everyone.
>
> Regards,
> Kals
>
>
> On Wed, 31 Dec 2003 00:20:21 +0100, Bernd Oppolzer <[login to unmask email]
> ONLINE.DE> wrote:
>
> >-104 is a syntax error. If you really got -104, I would try to
> >use a table alias on the update statement and use this alias in the
> subquery. I
> >always do it that way, but I'm not sure if it is only a question of style
> or a
> >real requirement.
> >
> >like this:
> >
> >update t1 a
> > set c2 = (select c2
> > from t2
> > where c1 = a.c1);
> >
> >and: I don't think that the subquery gives a NULL value, if no row can
> >be found. I believe, you will get a very specific error, if the subquery
> >finds no (or more than one) row. So it should be no problem, if c2 is not
> >nullable, as long as you don't get a real NULL value from the subquery).
> >
> >It is never wrong to add a WHERE EXISTS condition to the UPDATE, as
> >others have suggested. If you can get more than one row in the subquery,
> >you may have to use max(c2) or anything of this kind in the subquery.
> >
> >HTH, regards
> >
> >Bernd
> >
> >
> >
> >Am Die, 30 Dez 2003 schrieben Sie:
> >> Table T1 has columns C1 and C2. Table T2 has columns
> >> C1 and C2. All columns are not-nullable.
> >>
> >> For rows in tables T1 and T2 that match on C1 (WHERE
> >> T1.C1 = T2.C1), I want to update T1.C2=T2.C2;
> >> something along similar lines:
> >>
> >> UPDATE T1
> >> SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)
> >>
> >> The above SQL fails with SQLCODE -104 (Column cannot
> >> contain a NULL value.) which I understand since C2 is
> >> not-nullable.
> >>
> >> The following doesn't work either and fails with the
> >> same SQLCODE and message:
> >>
> >> UPDATE T1
> >> SET C2 = (SELECT COALESCE(C2,'UNKNOWN') FROM T2 WHERE
> >> T1.C1=T2.C1)
> >>
> >> Any ideas as to how I can accomplish the update. The
> >> following works but in my opinion is incredibly
> >> complex considering the simple requirement I have:
> >>
> >> UPDATE T1
> >> SET C2 =
> >> (SELECT T2.B FROM
> >> (
> >> SELECT T1.*, VALUE(T2.C2,'UNKNOWN') AS B
> >> FROM T1 LEFT OUTER JOIN T2
> >> ON T1.C1=T2.C1
> >> ) T2
> >> WHERE T1.C1 = T2.C1
> >> )
> >> ;
> >>
> >> There has to be a simple way. Requirement seems so
> >> simple.
> >>
> >> TIA
> >> Raquel.
> >>
> >> __________________________________
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm