Noobie SQL question

I hope this is the right group to ask this question. If not please
direct me to a more appropriate group.
I'm really new at SQL so bear with me.
I'll try to simplify the situation as best as I can...
Three files, 2 columns in each file. File one columns are item number
and current cost. File 2 columns are item number and last cost. File 3
columns are item number and item type. Each file has the same number of
records. One field in file two is null because it is a kit which does
not have a last cost associated with it. The 'kit' item number is
associated with an item type of 'K' in file 3.
I'm trying to update the current cost in file one with the last cost in
file 2. The command I've tried errors out when it gets to the 'kit'
record in file 2 because the current cost in file one can not be null.
How do I go about selecting only the non kit records in file 2 based on
the selection criteria available in file 3?
Any help or guidance would be greatly appreciated.
JimWhen you say "file", are you talking about "tables"? Or actual "files" in a
filesystem?
If you're talking about actual "tables", posting your DDL and some sample
data will help people to help you with a specific answer to your question.
Thanks
"Brass Balls" <me@.hotmail.com> wrote in message
news:d69Xd.1241$jW6.1155@.fe2.columbus.rr.com...
>I hope this is the right group to ask this question. If not please
> direct me to a more appropriate group.
> I'm really new at SQL so bear with me.
> I'll try to simplify the situation as best as I can...
> Three files, 2 columns in each file. File one columns are item number
> and current cost. File 2 columns are item number and last cost. File 3
> columns are item number and item type. Each file has the same number of
> records. One field in file two is null because it is a kit which does
> not have a last cost associated with it. The 'kit' item number is
> associated with an item type of 'K' in file 3.
> I'm trying to update the current cost in file one with the last cost in
> file 2. The command I've tried errors out when it gets to the 'kit'
> record in file 2 because the current cost in file one can not be null.
> How do I go about selecting only the non kit records in file 2 based on
> the selection criteria available in file 3?
> Any help or guidance would be greatly appreciated.
> Jim
>|||Showing my unfamiliarity with the language I was using table and file
interchangeably. What's the difference?
Anyway here's what I'm starting with:
Table 1:
ITNBR CMAT
1000 1.00
1001 1.50
2000 3.00
9999 5.00
Table 2:
ITNBR LCOST HOUSE
1000 2.00 1
1001 2.75 2
2000 3.00 1
9999 1
Table 3:
ITNBR ITTYP
1000 2
1001 2
2000 7
9999 K
I'm trying to update the CMAT field in table 1 with the LCOST field in table
2
if the HOUSE field in table 2 equals 1. The CMAT field can not be a NULL
value. There is no LCOST associated with ITNBR 9999 in table 2 because it i
s a
kit and not a separately purchased item. Item types are designated in table
3:
2=purchased, 7=manufactured, K=kit.
The following command errors out when trying to update CMAT with the NULL va
lue
in table 2:
update amflibx/itemasb f1
set cmat = (select lcost from amflibx/itembl f2
where f1.itnbr = f2.itnbr and f2.house = '1')
where f1.itnbr in (select itnbr from amflibx/itemasa f3
where f3.ittyp <> 'K')
The final result should be:
Table 1:
ITNBR CMAT
1000 2.00
1001 1.50
2000 3.00
9999
Any suggestions?
Jim
In article <bL9Xd.10974$nL5.5877@.fe11.lga>, xyz@.abcdef.com says...
>
>When you say "file", are you talking about "tables"? Or actual "files" in
a
>filesystem?
>If you're talking about actual "tables", posting your DDL and some sample
>data will help people to help you with a specific answer to your question.
>Thanks
>"Brass Balls" <me@.hotmail.com> wrote in message
>news:d69Xd.1241$jW6.1155@.fe2.columbus.rr.com...
>|||"Jim" <you@.somehost.com> wrote in message
news:422df802$0$30430$bb4e3ad8@.newscene.com...
> Showing my unfamiliarity with the language I was using table and file
> interchangeably. What's the difference?
>
OK, I thought you might be referring to tables, just wanted to make sure. A
'file' is an actual physical file, like a comma-delimited or other file on
your hard drive. You might see references on here to people importing or
exporting flat files or XML files. Other than your SQL MDF database file
and LDF log file, "files" are generally external to your database. Tables
are internal to the database.

> Anyway here's what I'm starting with:
> Table 1:
<snip>
> 9999 K
>
Just so you know, it's generally considered a good idea to post your actual
DDL (Data Definition Language) code so that people trying to help can
cut-and-paste to create the tables locally on their servers. Helps
troubleshoot your problem more quickly. You can generate your DDL using SQL
Ent Manager's Generate SQL Script function.

> I'm trying to update the CMAT field in table 1 [itemsab] with the LCOST
> field in table 2 [itemtbl]
> if the HOUSE field in table 2 [itemtbl] equals 1.
> The CMAT field can not be a NULL
> value. [You have it set as a NULL value in your example for item 9999?]
> There is no LCOST associated with ITNBR 9999 in table 2 because it is a
> kit and not a separately purchased item. [You have to decide if your
> business rules
> allow NULL values in CMAT or not...] Item types are designated in table
> 3:
> 2=purchased, 7=manufactured, K=kit.
> The following command errors out when trying to update CMAT with the NULL
> value
> in table 2:
> update amflibx/itemasb f1
> set cmat = (select lcost from amflibx/itembl f2
> where f1.itnbr = f2.itnbr and f2.house = '1')
> where f1.itnbr in (select itnbr from amflibx/itemasa f3
> where f3.ittyp <> 'K')
>
Not sure where the forward slashes are coming from, but here's a version
that *almost* gives the results you want:
UPDATE itemsab
SET CMAT = b.tempLCost
FROM
(
SELECT f1.itnbr AS PartNum, COALESCE(f2.lcost, 0) AS tempLCost, f3.ittyp
FROM itemsab f1 INNER JOIN itemtbl f2
ON f1.itnbr = f2.itnbr
INNER JOIN itemasa f3
ON f1.itnbr = f3.itnbr
AND f2.house = 1 AND f3.ittyp <> 'K'
) b
WHERE itnbr = b.PartNum
The reason it's almost what you want is that your criteria are in conflict
with one another. You say that the CMAT cannot be NULL, yet you try to set
it to NULL. It is probably defined as NOT NULL in the database. If you
want to, you can assign a 0 to it instead of a NULL using the COALESCE()
function as above. This query, with your sample data updates only 2 rows -
it does not update the 9999 item since we specifically exclude 'K' item
types. If you want it to update all House Types of 1, including 'K' types,
like 9999, try:
UPDATE itemsab
SET CMAT = b.tempLCost
FROM
(
SELECT f1.itnbr AS PartNum, COALESCE(f2.lcost, 0) AS tempLCost, f3.ittyp
FROM itemsab f1 INNER JOIN itemtbl f2
ON f1.itnbr = f2.itnbr
INNER JOIN itemasa f3
ON f1.itnbr = f3.itnbr
AND f2.house = 1
) b
WHERE itnbr = b.PartNum
If you want to be able to assign NULL values to CMAT field, you need to make
sure it is set to Allow NULL values. (*Not* NOT NULL). Then you can drop
the COALESCE():
UPDATE itemsab
SET CMAT = b.tempLCost
FROM
(
SELECT f1.itnbr AS PartNum, f2.lcost AS tempLCost, f3.ittyp
FROM itemsab f1 INNER JOIN itemtbl f2
ON f1.itnbr = f2.itnbr
INNER JOIN itemasa f3
ON f1.itnbr = f3.itnbr
AND f2.house = 1
) b
WHERE itnbr = b.PartNum

> The final result should be:
> Table 1:
> ITNBR CMAT
> 1000 2.00
> 1001 1.50
> 2000 3.00
> 9999
>
Hope that helps.|||Mike,
Thanks for your efforts in helping me working this out.
Isn't there a way to select only records in table 2 with non null values in
'lcost' based on a linkage to 'itnbr's in table 3 where the 'ittyp' does not
equal 'K'?
And then using only those records in table 2 to update the records in table
1.
Thinking while I'm typing can't this check for not null values in 'lcost' be
made part of the first selection criteria and abandon the references to tabl
e 3
altogether?
Jim
In article <%suXd.10583$Sq7.6550@.fe09.lga>, xyz@.abcdef.com says...
>
>"Jim" <you@.somehost.com> wrote in message
>news:422df802$0$30430$bb4e3ad8@.newscene.com...
>OK, I thought you might be referring to tables, just wanted to make sure.
A
>'file' is an actual physical file, like a comma-delimited or other file on
>your hard drive. You might see references on here to people importing or
>exporting flat files or XML files. Other than your SQL MDF database file
>and LDF log file, "files" are generally external to your database. Tables
>are internal to the database.
>
><snip>
>Just so you know, it's generally considered a good idea to post your actual
>DDL (Data Definition Language) code so that people trying to help can
>cut-and-paste to create the tables locally on their servers. Helps
>troubleshoot your problem more quickly. You can generate your DDL using SQ
L
>Ent Manager's Generate SQL Script function.
>
>Not sure where the forward slashes are coming from, but here's a version
>that *almost* gives the results you want:
>UPDATE itemsab
>SET CMAT = b.tempLCost
>FROM
>(
> SELECT f1.itnbr AS PartNum, COALESCE(f2.lcost, 0) AS tempLCost, f3.ittyp
> FROM itemsab f1 INNER JOIN itemtbl f2
> ON f1.itnbr = f2.itnbr
> INNER JOIN itemasa f3
> ON f1.itnbr = f3.itnbr
> AND f2.house = 1 AND f3.ittyp <> 'K'
> ) b
>WHERE itnbr = b.PartNum
>The reason it's almost what you want is that your criteria are in conflict
>with one another. You say that the CMAT cannot be NULL, yet you try to set
>it to NULL. It is probably defined as NOT NULL in the database. If you
>want to, you can assign a 0 to it instead of a NULL using the COALESCE()
>function as above. This query, with your sample data updates only 2 rows -
>it does not update the 9999 item since we specifically exclude 'K' item
>types. If you want it to update all House Types of 1, including 'K' types,
>like 9999, try:
>UPDATE itemsab
>SET CMAT = b.tempLCost
>FROM
>(
> SELECT f1.itnbr AS PartNum, COALESCE(f2.lcost, 0) AS tempLCost, f3.ittyp
> FROM itemsab f1 INNER JOIN itemtbl f2
> ON f1.itnbr = f2.itnbr
> INNER JOIN itemasa f3
> ON f1.itnbr = f3.itnbr
> AND f2.house = 1
> ) b
>WHERE itnbr = b.PartNum
>If you want to be able to assign NULL values to CMAT field, you need to mak
e
>sure it is set to Allow NULL values. (*Not* NOT NULL). Then you can drop
>the COALESCE():
>UPDATE itemsab
>SET CMAT = b.tempLCost
>FROM
>(
> SELECT f1.itnbr AS PartNum, f2.lcost AS tempLCost, f3.ittyp
> FROM itemsab f1 INNER JOIN itemtbl f2
> ON f1.itnbr = f2.itnbr
> INNER JOIN itemasa f3
> ON f1.itnbr = f3.itnbr
> AND f2.house = 1
> ) b
>WHERE itnbr = b.PartNum
>
>Hope that helps.
>