Update multiple rows with data from two tables

Results 1 to 2 of 2

Thread: Update multiple rows with data from two tables

  1. #1
    Join Date
    Dec 1969

    Default Update multiple rows with data from two tables

    I am having to re ask a question that was partly answer on the 26th June. I am trying to update a new column with figures from another column. However, this data needs to be modified based on another Column labelled Condition.<BR>New column = Stock.StockPrice (obviously in Stock table)<BR>Data to copy from = Record.Price<BR>The Record.Price should be altered by the column Stock.Condition which contains &#039;Mint&#039; = 100% of Stock.Price, &#039;Excellent&#039; = 90% of Stock.Price, &#039;Very Good&#039; = 80% of Stock.Price and &#039;Good&#039; = 70% of Stock.Price.<BR><BR>I have created this code block but it isn&#039;t working:<BR><BR>&#060;!--#include file="../adovbs.inc"--&#062;<BR>&#060;!--#include file="../DatabaseConnect.asp"--&#062;<BR>&#060;%<BR>strSQL="UPDATE Stock SET StockPrice = Record.Price * 1 WHERE Stock.CatNo=Record.CatNo AND Condition = &#039;Excellent&#039;" <BR>Set objRS = Server.CreateObject ("ADODB.Recordset")<BR>objRS.ActiveConnection = objConn<BR>objRS.CursorLocation=2<BR>objRS.CursorT ype = 2<BR>objRS.LockType = 2<BR>objRS.Open strSQL<BR>objRS.Close<BR>%&#062;<BR><BR>How can I update all the tables from this data. The link between the two tables is named CatNo so there is Stock.CatNo and Record.CatNo.<BR><BR>Hope someone can help<BR><BR>Lloyd

  2. #2
    Join Date
    Dec 1969

    Default RE: Update multiple rows with data from two tables

    strSQL="UPDATE Stock SET StockPrice = case " & _<BR>" condition when &#039;Mint&#039; then 1.0 when &#039;Excellent&#039; then 0.9 " &_<BR>" when &#039;very Good&#039; then 0.8 when &#039;Good&#039; then 0.7 end " &_<BR>" * (select Price from record " & _<BR>" wHERE CatNo=Stock.CatNo)" <BR><BR>That&#039;s a standard SQL formulation. Some DBMS allows joins when doing updates, but those constructions are propietary and differs between DBMS

Posting Permissions

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