>From what I see, you are not trying to update two tables, but combine a search and an update in one action. For simplicity and portability, do it in two:
1. Create a search action to retrieve the from_Date from the course_crosslink table, using the correct course_ID, and assign it to a variable. 2. Create an update action that update courses.from_date with the variable. This should work, but it sounds like you some funky one to many relationship going on. How does the "from_date" link the tables in a way that the "course_id" does not? -- Robert Garcia BigHead Technology 21053 Devonshire Suite 206 Chatsworth, Ca 91311 Phone 818.773.8162 Fax 818.773.8164 http://www.bighead.net/ [EMAIL PROTECTED] > From: Dan Stein <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Mon, 20 May 2002 21:36:18 -0400 > To: Multiple recipients of list witango-talk <[EMAIL PROTECTED]> > Subject: Witango-Talk: Another SQL conundrum > > I am trying to fix a screw up where my app has not been entering data. I can > pull all this stuff into excel or FMP and then manipulate it and put it back > but I would bet there is a way to do it with SQL. > > So here goes for all you guru's out there. > > I have a table called courses > And a table called course_crosslink > > The crosslink table holds information about the course data and time so I > can deal with multi-day courses. > > So problem one may be that the course_ID from courses has a one to many > relationship with the course_ID from crosslink. > > What I want to do is set a column called From_Date in courses to the > Course_Date column in crosslink. I fixed the taf so it does this properly > when it creates the course record but now I have a legacy issue to deal > with. > > 1, I was trying to do this which would be the easiest and I would expect it > to grab the first course_date when there were duplicate course_ID values. > > Update courses > Set courses.from_date=course_crosslink.Course_date > Where courses.course_ID=course.crosslink.course_ID > > It pareses fine but when I execute it I get the error message the column > prefix course_crosslink does not match with a table name or alias used in > the query. > > I know I have written things like this in the past and they have worked > somewhere I must be missing something I guess where I need to define the > tables but I don't know where it goes. > > > 2. As a test I took my development data a created another table with > course_ID as a primary key and no duplicate values. But the Update still > does not work. > > With 288 courses I would like to do this through SQL and since it is > supposed to go live Wednesday I am in a bit of a crunch. > > Dan > > -- > Dan Stein > Digital Software Solutions > 799 Evergreen Circle > Telford PA 18969 > 215-799-0192 > 610-256-2843 > Fax 413-410-9682 > FMP,Tango, EDI,SQL 7 > [EMAIL PROTECTED] > www.dss-db.com > > > ________________________________________________________________________ > TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > with unsubscribe witango-talk in the message body > ________________________________________________________________________ TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
