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

query help

P: n/a
I have another complicated query I could use help on. Here is what
the tables look like:

Table1

col1 col2
Name Main_number
AA1 0
AA2 1
AA3 1
AA4 2
AA5 4
AA6 4
AA7 6
Table2

col1 col2
Name Sec_name
AA1 B00
AA3 B01
AA4 B00
AA5 B02
AA6 B02
AA7 B04
Table3

col1 col2
Name Pri_number
B00 5
B01 5
B02 6
B03 5
B04 6
B05 7

So basically what I want to do is to update Table1.Main_number with
Table2.Pri_number if Main_number is between the values of 1 and 4.

I tried to do with in a SELECT and UPDATE statements but couldnt get
the logic right. I first broke it down in pieces to get the logic
right:

1) find records that have a Main_number between 1 to 4
select name, main_number from table1 where main_number between 1 and
4

2) locate the name in table2
select name from table2 (where name equals name from table1)

3) match the name to a sec_name
select sec_name from table2 (where name matches from item 2 above)

4) find the sec_name from table2 and get the pri_number
select pri_number from table3 (where sec_name matches item 3 above)

5) use it to update the the main_number
update table1 set main_number = table2.pri_number
So when I tried putting it together it looked like this:

db2 "WITH
s1(name, main_number) AS (SELECT name, main_number FROM table1
WHERE main_number BETWEEN 1 AND 4),
s2(name) AS (SELECT name FROM table2 WHERE name IN (SELECT
name FROM s1)),
s3(sec_name) AS (SELECT sec_name FROM table2 WHERE name IN
(SELECT name FROM s2)),
s4(pri_number) AS (SELECT pri_number FROM table3 WHERE
sec_name IN (SELECT sec_name FROM s3)),
u1(results) AS (SELECT 1 FROM OLD TABLE (UPDATE table1 SET
main_number = (SELECT pri_number FROM s4)))
SELECT results FROM u1"
but it didnt work...received :

SQL0407N Assignment of a NULL value to a NOT NULL column
Any help would be appreciated. I am on DB2 UDB V8.2 if it matters.

Thanks!

Oct 17 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Oct 17, 10:44*am, shorti <lbrya...@juno.comwrote:
I have another complicated query I could use help on. *Here is what
the tables look like:

Table1

col1 * * * col2
Name * *Main_number
AA1 * * * 0
AA2 * * * 1
AA3 * * * 1
AA4 * * * 2
AA5 * * * 4
AA6 * * * 4
AA7 * * * 6

Table2

col1 * * * *col2
Name * * Sec_name
AA1 * * * *B00
AA3 * * * *B01
AA4 * * * *B00
AA5 * * * *B02
AA6 * * * *B02
AA7 * * * *B04

Table3

col1 * * *col2
Name * Pri_number
B00 * * *5
B01 * * *5
B02 * * *6
B03 * * *5
B04 * * *6
B05 * * *7

So basically what I want to do is to update Table1.Main_number with
Table2.Pri_number if Main_number is between the values of 1 and 4.

I tried to do with in a SELECT and UPDATE statements but couldnt get
the logic right. *I first broke it down in pieces to get the logic
right:

1) find records that have a Main_number between 1 to 4
select name, main_number from table1 where main_number between 1 and
4

2) locate the name in table2
select name from table2 (where name equals name from table1)

3) match the name to a sec_name
select sec_name from table2 (where name matches from item 2 above)

4) find the sec_name from table2 and get the pri_number
select pri_number from table3 (where sec_name matches item 3 above)

5) use it to update the the main_number
update table1 set main_number = table2.pri_number

So when I tried putting it together it looked like this:

db2 "WITH
* * * * s1(name, main_number) AS (SELECT name, main_number FROM table1
WHERE main_number BETWEEN 1 AND 4),
* * * * s2(name) AS (SELECT name FROM table2 WHERE name IN (SELECT
name FROM s1)),
* * * * s3(sec_name) AS (SELECT sec_name FROM table2 WHERE name IN
(SELECT name FROM s2)),
* * * * s4(pri_number) AS (SELECT pri_number FROM table3 WHERE
sec_name IN (SELECT sec_name FROM s3)),
* * * * u1(results) AS (SELECT 1 FROM OLD TABLE (UPDATE table1 SET
main_number = (SELECT pri_number FROM s4)))
SELECT results FROM u1"

but it didnt work...received :

SQL0407N *Assignment of a NULL value to a NOT NULL column

Any help would be appreciated. *I am on DB2 UDB V8.2 if it matters.

Thanks!
try this...
=====================
MERGE INTO Table1 dest
USING (
select
table2.name as name1,
table3.name as name2,
pri_number,
sec_name
from
table2, table3
where
table2.sec_name = table3.name
) src
ON (dest.name = src.name1)
AND (dest.main_number between 1 and 4 )
WHEN MATCHED THEN
UPDATE SET
dest.main_number = src.pri_number
;
=====================
Oct 17 '08 #2

P: n/a
On Oct 17, 9:24*am, "mail2nee...@gmail.com" <mail2nee...@gmail.com>
wrote:
On Oct 17, 10:44*am, shorti <lbrya...@juno.comwrote:


I have another complicated query I could use help on. *Here is what
the tables look like:
Table1
col1 * * * col2
Name * *Main_number
AA1 * * * 0
AA2 * * * 1
AA3 * * * 1
AA4 * * * 2
AA5 * * * 4
AA6 * * * 4
AA7 * * * 6
Table2
col1 * * * *col2
Name * * Sec_name
AA1 * * * *B00
AA3 * * * *B01
AA4 * * * *B00
AA5 * * * *B02
AA6 * * * *B02
AA7 * * * *B04
Table3
col1 * * *col2
Name * Pri_number
B00 * * *5
B01 * * *5
B02 * * *6
B03 * * *5
B04 * * *6
B05 * * *7
So basically what I want to do is to update Table1.Main_number with
Table2.Pri_number if Main_number is between the values of 1 and 4.
I tried to do with in a SELECT and UPDATE statements but couldnt get
the logic right. *I first broke it down in pieces to get the logic
right:
1) find records that have a Main_number between 1 to 4
select name, main_number from table1 where main_number between 1 and
4
2) locate the name in table2
select name from table2 (where name equals name from table1)
3) match the name to a sec_name
select sec_name from table2 (where name matches from item 2 above)
4) find the sec_name from table2 and get the pri_number
select pri_number from table3 (where sec_name matches item 3 above)
5) use it to update the the main_number
update table1 set main_number = table2.pri_number
So when I tried putting it together it looked like this:
db2 "WITH
* * * * s1(name, main_number) AS (SELECT name, main_number FROMtable1
WHERE main_number BETWEEN 1 AND 4),
* * * * s2(name) AS (SELECT name FROM table2 WHERE name IN (SELECT
name FROM s1)),
* * * * s3(sec_name) AS (SELECT sec_name FROM table2 WHERE nameIN
(SELECT name FROM s2)),
* * * * s4(pri_number) AS (SELECT pri_number FROM table3 WHERE
sec_name IN (SELECT sec_name FROM s3)),
* * * * u1(results) AS (SELECT 1 FROM OLD TABLE (UPDATE table1 SET
main_number = (SELECT pri_number FROM s4)))
SELECT results FROM u1"
but it didnt work...received :
SQL0407N *Assignment of a NULL value to a NOT NULL column
Any help would be appreciated. *I am on DB2 UDB V8.2 if it matters.
Thanks!

try this...
=====================
*MERGE INTO Table1 dest
* * * * USING (
* * * * * * * * select
* * * * * * * * * * * * table2.name as name1,
* * * * * * * * * * * * table3.name as name2,
* * * * * * * * * * * * pri_number,
* * * * * * * * * * * * sec_name
* * * * * * * * from
* * * * * * * * * * * * table2, table3
* * * * * * * * where
* * * * * * * * * * * * table2.sec_name = table3.name
* * * * * * * * ) src
* * * * ON *(dest.name = src.name1)
* * * * AND (dest.main_number between 1 and 4 )
* * * * WHEN MATCHED THEN
* * * * * * * * UPDATE SET
* * * * * * * * * * * * dest.main_number = src.pri_number
* * * * ;
=====================- Hide quoted text -

- Show quoted text -
Thanks so much ...this worked perfectly.
Oct 17 '08 #3

P: n/a
select
table2.name as name1,
table3.name as name2,
pri_number,
sec_name
from
table2, table3
where
table2.sec_name = table3.name
I thought that "table3.name as name2" and "sec_name" were not
neccesary.
Oct 17 '08 #4

P: n/a
select
table2.name as name1,
table3.name as name2,
pri_number,
sec_name
from
table2, table3
where
table2.sec_name = table3.name
I thought that "table3.name as name2" and "sec_name" in the select
list were not necessary.

Oct 18 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.