SQL, filling a table with matched Id's??

Results 1 to 2 of 2

Thread: SQL, filling a table with matched Id's??

  1. #1
    Join Date
    Dec 1969

    Default SQL, filling a table with matched Id's??

    Okay, here is my dilemma: I am working on a database that was built rather poorly. And one of the things that has me stumped is as follows - I have the tables: <BR><BR>Process Operator <BR>------- -------- <BR>ID ID <BR>Desc OperatorName <BR>OperName Dept <BR>Operator2 <BR><BR>The Operator table did not have the ID column, it was just the Operator&#039;s name. So I added an autonumber column to it, so that it would be easier to work with. However, the Process table has the previously entered Operators as OperName, so I added a column Operator2. I want to fill up Operator2 with the ID&#039;s that match the names under OperName (according to the Operator table). Here is my SQL, but it does not work - what am I doing wrong here? <BR><BR>UPDATE Process<BR>SET Operator2 = <BR>(SELECT o.ID <BR>FROM Operator o <BR>WHERE o.OperatorName = OperName ) <BR><BR>It returns an error that my subquery is returning more than one recordset. Here is an example of what I would like it to do:<BR><BR>OPERATOR<BR>ID &#124 OperatorName &#124 Dept<BR>1 &#124 Joe Blow &#124 Janitorial<BR>2 &#124 Jack Daniels &#124 Custodial<BR><BR>PROCESS<BR>ID &#124 DESC &#124 OPERNAME &#124 Operator2<BR>102 &#124 Clean toilets &#124 Joe Blow &#124<BR>107 &#124 Serve drinks &#124 Jack Daniels &#124<BR><BR>I want to fill Operator2 with the corresponding Id&#039;s (1, and 2 respectively) ....

  2. #2
    Join Date
    Dec 1969

    Default Repost. <eop>


Posting Permissions

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