Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2012
    Posts
    12

    Unanswered: How to update another field from a 'combo' field...

    ... la Access?

    In Access, you have a combo with column designations for example me.combofield.column(x) and you can update another field with those column(x) values.

    How do you do it in MS SQL?

    EDIT: I didn't mean "from a 'combo' field'" in SQL(!) I just want to reproduce the equivalent of an Access combo box.
    Last edited by Saintor; 11-01-12 at 08:38. Reason: Clarification

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL doesn't even have combo boxes.
    I don't think you are phrasing your question well, and you might consider posting it in the Access forum instead.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2012
    Posts
    12
    I tried this in my trigger... won't work. EmplId is integer

    UPDATE tblempl01
    SET tblempl01.EmplName = tblEmpl.EmplName
    FROM tblempl01
    INNER JOIN tblEmpl
    ON tblempl01.emplid = tblEmpl.emplid

  4. #4
    Join Date
    Sep 2012
    Posts
    12
    Evil can be in details.

    UPDATE tblEmpl01
    SET EmplName = tblEmpl.EmplName
    FROM tblEmpl01
    INNER JOIN tblEmpl
    ON tblEmpl01.EmplId = tblEmpl.EmplId
    WHERE tblEmpl01.EmplId = tblEmpl.EmplId

    It now works.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No.
    "ON tblEmpl01.EmplId = tblEmpl.EmplId" is logically equivalent to "WHERE tblEmpl01.EmplId = tblEmpl.EmplId"
    It is not necessary to use both.
    Your second statement will not execute any differently than your first statement.

    The error you are getting lies elsewhere, and is probably due to the fact that you aren't referencing the virtuatl "INSERTED" and "DELETED" tables in your trigger.
    Please read the article in Books Online regarding triggers before you proceed further.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2012
    Posts
    12
    What can I say? It just works and it didn't before. I added WHERE in addition to ON. And I refer to EmplName instead of tblempl01.EmplName

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Saintor, it looks like you are fooling yourself into thinking you've accomplished something.

    Do you really have this statement in a trigger ???

    If so, you have some serious design issues with your system.

    Basically, all your UPDATE statement is doing is:

    . . . every time someone "touches" your employee table, this statement makes sure that every record in that employee table, and a similar employee table have matching employee names where they have common employee IDs.

    It's not selectively updating records, as it should. Instead, it is doing a mass update of all employee records--very inefficient. And it is updating every record in the matching table even if you "touch" any other field other than the employee name field; obviously, if you change a date of hire field, you don't need to update the employee name field in every record in a table--in fact you don't have to update a single record.

    It's kind of like, you have to change the oil in your car, so you've decided to replace the engine 'cause you know a new engine has fresh oil in it.

    You may have done something, but this is not the way to do whatever you are attempting to do.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  8. #8
    Join Date
    Sep 2012
    Posts
    12
    Quote Originally Posted by PracticalProgram View Post
    Saintor, it looks like you are fooling yourself into thinking you've accomplished something.
    Keep this kind of nonsense for yourself.

    Don't worry about massive update. I will add the proper criteria as needed. The code above was for testing only.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Saintor View Post
    The code above was for testing only.
    Oh, I juve LOVE posters like you! You ought to have just told us the truth, something like:
    Quote Originally Posted by Saintor View Post
    Here, debug this crap that I posted... I'll let you know what happens when I apply what I think you meant to the code that I'm really working with.
    Thanks for posting!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Sep 2012
    Posts
    12
    I thought that it was a more serious forum than some idiots with smart-ass remarks.

    My bad.

    Funny part is that nobody proposed a better solution.
    Last edited by Saintor; 11-02-12 at 22:34.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Saintor View Post
    Funny part is that nobody proposed a better solution.
    Let us know when you figure out that we already did, more than once.

    If you have a problem with our response that is based on facts (for instance if you think that we're not understanding you or that our analysis is faulty), we'll try to address that. If you want to call us idiots because we pointed out what we see as offensive behavior on your part, please go whine somewhere else.

    You appear to have already conceeded in a previous post that you posted something other than the code that was actually causing your problems. You also claim that when you made unspecified changes to the real code that would not have changed the behavior of the code that you posted, the behavior improved.

    If my assumptions are correct, we know for certain that we can't trust what you post because it probably isn't the actual code causing your problems. If my assumptions are not correct, then set me straight. Either way, you have some serious work to do to repair your reputation in order to get any effort from me to help you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Sep 2012
    Posts
    12
    Quote Originally Posted by Pat Phelan View Post
    . Either way, you have some serious work to do to repair your reputation in order to get any effort from me to help you.

    -PatP
    *Good!* Stay out of my threads. Your idiotic comments are just disturbing this forum.

  13. #13
    Join Date
    Sep 2012
    Posts
    12
    I finally got another solution from somebody (helpful this time) who sent me an email.

    Basically use a view and triggers "Instead Of" rather than "After" for every events (update, insert, delete). And of course avoiding at the same time maintaining non-essential fields. Good info.

  14. #14
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    *Good!* Stay out of my threads. Your idiotic comments are just disturbing this forum.
    Maybe you should not post any more "threads" here . . . This one may be one too many.

    It will be quite easy to do as you ask.

    You should do a little "background" investigation and ensure that when you post as a whiny little spoiled individual, you do not do so in a reply to one of the most knowledgable and helpful people supporting the forum.

    Keep in mind that Everyone makes the forum better - some by joining and properly participating and other by leaving . . .

  15. #15
    Join Date
    Sep 2012
    Posts
    12
    Quote Originally Posted by papadi View Post
    Maybe you should not post any more "threads" here . . . This one may be one too many.

    It will be quite easy to do as you ask.

    You should do a little "background" investigation and ensure that when you post as a whiny little spoiled individual, you do not do so in a reply to one of the most knowledgable and helpful people supporting the forum.

    Keep in mind that Everyone makes the forum better -
    Not you apparently.

Posting Permissions

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