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! 4 2551
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
;
=====================
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.
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
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...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
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: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
| |