query help

[shorti]

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!

[mail2neeraj]

On Oct 17, 10:44*am, shorti <[e-mail]:

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
;
=====================

[shorti]

On Oct 17, 9:24*am, "[e-mail]" <[e-mail]>
wrote:

On Oct 17, 10:44*am, shorti <[e-mail]:

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.

[Tonkuma]

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.

[Tonkuma]

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.