home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #30 / NN_1992_30.iso / spool / comp / database / 8460 < prev    next >
Encoding:
Internet Message Format  |  1992-12-12  |  2.2 KB
  1. Xref: sparky comp.databases:8460 comp.databases.oracle:2495
  2. Path: sparky!uunet!usc!wupost!cs.utexas.edu!qt.cs.utexas.edu!yale.edu!jvnc.net!netnews.upenn.edu!msuinfo!oak!newton!strobel
  3. From: strobel@newton.mcs.gvsu.edu (Stephen Strobel)
  4. Newsgroups: comp.databases,comp.databases.oracle
  5. Subject: Re: Weird ORACLE update statement
  6. Message-ID: <1992Dec12.143257.8808@mcs.gvsu.edu>
  7. Date: 12 Dec 92 14:32:57 GMT
  8. References: <1992Dec8.153620.9115@infonode.ingr.com> <1992Dec8.210428.22025@cbnewsk.cb.att.com>
  9. Sender: news@mcs.gvsu.edu
  10. Distribution: na
  11. Organization: Grand Valley State University, Allendale MI
  12. Lines: 47
  13. Nntp-Posting-Host: newton
  14.  
  15. In article <1992Dec8.210428.22025@cbnewsk.cb.att.com> jrmo@prpix2.att.com (J.R.Moore) writes:
  16. >In article <1992Dec8.153620.9115@infonode.ingr.com> mcintyre@infonode.ingr.com (John Scott Mcintyre) writes:
  17. >>    I am using ORACLE and want to be able to update rows in table1 using
  18. >>    columns from table2 when the two tables can be joined.
  19. >>
  20. >>    The SQL statement I would like to perform would look like this:
  21. >>
  22. >>    update table1 set table1.manufacturer=table2.manufact,
  23. >>              set table1.pur_cost=table2.sale_cost
  24. >>        where table1.inv_number=table2.inv_number;
  25. >>
  26. >>    The problem is UPDATE apparently cannot handle multiple tables.
  27. >>
  28. >Try this:
  29. >
  30. >UPDATE table1 A
  31. >SET     manufacturer=(
  32. >        SELECT table2.manufact
  33. >        FROM table2
  34. >        WHERE A.inv_number=table2.inv_number
  35. >        ),
  36. >    pur_cost=(
  37. >        SELECT table2.sale_cost
  38. >        FROM table2
  39. >        WHERE A.inv_number=table2.inv_number
  40. >        )
  41. >WHERE    inv_number IN (
  42. >        SELECT table2.inv_number
  43. >        FROM table1,table2
  44. >        WHERE table1.inv_number=table2.inv_number
  45. >        )
  46. >;
  47. >
  48. >Joe Moore
  49.  
  50. Better yet:
  51.  
  52. Update Table1 A
  53. Set (manufacturer, pur_cost) =
  54.     (Select manufacturer, sale_cost
  55.      from   table2 B
  56.      where  A.inv_number = B.inv_number)
  57. -- 
  58. |--------------------------------------|--------------------------------------|
  59. |  Stephen Strobel                     | Phone: (h) (616) 235-1263            |
  60. |  Grand Valley State University       |        (w) (616) 940-4241            | |                                      |  Mail: strobel@newton.mcs.gvsu.edu   |
  61. |--------------------------------------|--------------------------------------|
  62.