Update table with join?

Results 1 to 2 of 2

Thread: Update table with join?

  1. #1
    Join Date
    Dec 1969

    Default Update table with join?

    Hi everyone!<BR>Any help would be appreciated with this one. Please see below.<BR><BR>How will can i<BR>- Update the PRICE column in table 2.<BR> That is, the AVG(PART_NO) in table1 to TABLE2<BR> where table2.PART_NO = table1.PART_NO<BR><BR><BR>Here are the tables and and example of what it can contain:<BR>[TABLE1].<BR>part_no<BR>original_part_no<BR>manufacturer<B R>price<BR><BR>PART_NO, ORIGINAL_PART_NO, MANUFACTURER, PRICE<BR>100, 12345, Man1, 5000$<BR>100, 11111, Man2, 4500$<BR>100, 22222, Man3, 5600$<BR><BR>[TABLE2]<BR>part_no (PK)<BR>original_part_no<BR>manufacturer<BR>priceE <BR><BR>PART_NO, ORIGINAL_PART_NO, MANUFACTURER, PRICE<BR>100 (PK),12345, Man1, 5000$<BR>

  2. #2
    Garth Guest

    Default RE: Update table with join?

    The statement needed to do the update is shown below.<BR><BR>Garth<BR>www.SQLBook.com<BR><BR>--SQL Script<BR>SET NOCOUNT ON<BR><BR>CREATE TABLE Table1<BR>(<BR> PartNo int NOT NULL,<BR> PurchasePrice money NOT NULL<BR>)<BR>go<BR>INSERT Table1 VALUES (1,10)<BR>INSERT Table1 VALUES (1,20)<BR>INSERT Table1 VALUES (1,30)<BR>INSERT Table1 VALUES (2,1)<BR>INSERT Table1 VALUES (2,3)<BR>INSERT Table1 VALUES (2,5)<BR><BR><BR>CREATE TABLE Table2 <BR>(<BR> PartNo int PRIMARY KEY,<BR> AvgPurchasePrice money NULL<BR>)<BR>go<BR>INSERT Table2<BR>SELECT DISTINCT PartNo,NULL<BR>FROM Table1<BR><BR><BR>UPDATE Table2<BR>SET AvgPurchasePrice = (SELECT AVG(PurchasePrice)<BR> FROM Table1<BR> WHERE Table2.PartNo = Table1.PartNo)<BR><BR>SELECT * FROM Table2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts