473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

query help

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
4 2550
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
7
by: Simon Bailey | last post by:
How do you created a query in VB? I have a button on a form that signifies a certain computer in a computer suite. On clicking on this button i would like to create a query searching for all...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
4
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table...
36
by: Liam.M | last post by:
hey guys, I have one last problem to fix, and then my database is essentially done...I would therefore very much appreciate any assistance anyone would be able to provide me with. Currently I...
5
by: elitecodex | last post by:
Hey everyone. I have this query select * from `TableName` where `SomeIDField` 0 I can open a mysql command prompt and execute this command with no issues. However, Im trying to issue the...
10
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
4
by: Doris | last post by:
It does not look like my message is posting....if this is a 2nd or 3rd message, please forgive me as I really don't know how this site works. I want to apologize ahead of time for being a novice...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.