Update each row in a query with information from a different table 1

[AnnaWnz]

Hi all

I am trying to update a field in one table with information from
another table based on a few restrictions.

Table 1: holds the fields ITEMNUMBER and VENDORID
Table 2: holds the fields VENDORID and VENDORCLASS
Table 3: holds the fields ITEMNUMBER and CATEGORY

The restrictions include:
1. Don't run on rows where there are duplicate records in Table 1.
That is, where there is more than one VENDORID for an ITEMNUMBER, the
ITEMNUMBER is listed as a separate record for each VENDORID associated
with it.

2. The value of VENDORCLASS needs to be linked to the ITEMNUMBER in
Table 3, for the restriction below to work. That is, if run a query
pulling together the 3 tables, excluding duplicates, get one line for
each item.

3. Update the value of the field CATEGORY based on the value of the
field VENDORCLASS, where if VENDORCLASS = XX then CATEGORY = XX, and
if VENDORCLASS <> XX then CATEGORY = ZZ

Queries I've created for the above:
1. select ITEMNUMBER from Table1 group by ITEMUNMBER having count(*) >
1
This gives me a list of the duplicate records in Table 1. This works
fine, but I somehow need to include it to exclude these items when I
run the update.

2. Select c.ITEMNUMBER, c.CATEGORY, a.VENDORID, b.VENDCLASS from
Table1 a, Table2 b, Table3 c where (a.ITEMNUMBER = c.ITEMNUMBER and
a.VENDORID = b.VENDORID) order by c.ITEMNUMBER
This gives me a list of the ITEMNUMBER, CATEGORY, VENDORID associated,
VENDORCLASS, but shows duplicate records. Have tried different things
to try and incorporate statement #1 into this but can't get it to
work.

3. Then need to update the field, and when this runs it puts the same
value into all the records lines regardless, rather than based on the
VENDORCLASS of the associated VENDORID.


Any help much appreciated.

Cheers

Anna

 

[Bobalooey]

Anna,

The followng should work

1. select ITEMNUMBER from Table1 group by ITEMUNMBER having count(*) > 1
This gives me a list of the duplicate records in Table 1. This works fine, but I somehow need to include it to exclude these items when I
run the update.

First isolate those ITEMNUMBER's to exclude those that occur more than once (You got half of it so far). I'd put your results from each step into a temporary table to make it easier to walk through it. Oh...and you might as well drag in VENDORCLASS at this point with it:

select
Table1.*,
Table2.VENDORCLASS
into #TEMP1
from Table1
left join Table2 on Table1.VENDORID = Table2.VENDORID
where ITEMNUMBER not in (select ITEMNUMBER from Table1 group by ITEMNUMBER having count(*) > 1)


Check your results so far with a simple:


Select * from #TEMP1

2. Select c.ITEMNUMBER, c.CATEGORY, a.VENDORID, b.VENDCLASS from
Table1 a, Table2 b, Table3 c where (a.ITEMNUMBER = c.ITEMNUMBER and
a.VENDORID = b.VENDORID) order by c.ITEMNUMBER
This gives me a list of the ITEMNUMBER, CATEGORY, VENDORID associated,
VENDORCLASS, but shows duplicate records. Have tried different things
to try and incorporate statement #1 into this but can't get it to
work.

3. Then need to update the field, and when this runs it puts the same
value into all the records lines regardless, rather than based on the
VENDORCLASS of the associated VENDORID.


Now you should be able to incorporate the results from #1 into the query. (I tested this myself and it works like a charm.)


UPDATE Table3
Set CATEGORY =
CASE #TEMP1.VENDORCLASS
when 'XX' then 'XX'
ELSE 'ZZ'
END
from #TEMP1, Table3
where #TEMP1.ITEMNUMBER = Table3.ITEMNUMBER


Again check your results


Select * from Table3


And drop the #TEMP1 table


DROP Table #TEMP1


Now go to lunch ;-)

 

[AnnaWnz]

Thanks so much. Worked perfectly!!

Anna