[thelist] ASP & HTML: Pass double data from a Select

rudy rudy937 at rogers.com
Thu Jul 17 18:48:49 CDT 2003


> "UPDATE users SET favorite_fruit = ???? WHERE user_id = 1"
>
> I assume that the ???? will be the join?  I'm no SQL jockey.... I could
> easily do this by first doing a seperate select:
>
> fruitPK = "SELECT fruit_id FROM fruit WHERE fruit_name = "Apple""
>
> And then make the Update look like this:
>
> "UPDATE users SET favorite_fruit = "&fruitPK&" WHERE user_id = 1"
>
> But the entire purpose here is to do this w/o two trips to the db, right?
> So it boils down to this: what is the syntax to build the join?


sorry for the delay answering this, casey

also, i was out to lunch when i suggested a join

what i meant was a subquery

ordinarily, i would simply do it like this --

  UPDATE users
  SET favorite_fruit =
      (  SELECT fruit_id
         FROM fruit
           WHERE fruit_name = 'Apple' )
   WHERE user_id = 1

this may not, however, work in Access

if you get "operation must use an updatable query" then you may need to do
it in two steps

heck, two calls to the database is nothing to worry about

;o)



More information about the thelist mailing list