SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Update

  1. #1
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)

    Update

    Yikes, sorry about the subject... accidentally hit enter with focus on the submit button

    Hi,

    My leads table has a lead_id, counselor_id and market_id

    If a leads counselor_id is updated then the lead_id's market_id should also be updated to the market_id associated with the new counselor_id.

    The market2users table is simple it has market_ids mapped to user_ids.

    So if a leads counselor_id is currently set to 10005 and it is updated to 10004 then the leads market_id should update to the related counselor_id in the Market2users table.

    I am trying to do this update, it does not throw an error and only update the l.counselor_id but not the l.market_id.
    Code:
    UPDATE
        leads as l
    INNER JOIN markets2users as m2u
        ON m2u.user_id = l.counselor_id 
    SET
        l.counselor_id = 10004
        ,l.market_id = m2u.market_id
    WHERE
        l.lead_id = 1
    So even though I am joining the market2users table to the leads counselor_id is it not finding the correct market2user mapping because it matches the current counselor_id rather than the l.counselor_id that will exist after the update? If so, can you suggest a way to do this?

    Thanks.
    ictus==""

  2. #2
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Well this is kind of ugly but it does the trick. It look like the answer to this quesiton is yes.
    So even though I am joining the market2users table to the leads counselor_id is it not finding the correct market2user mapping because it matches the current counselor_id rather than the l.counselor_id that will exist after the update?
    Here is what worked:
    Code:
    SELECT * FROM `users`.`leads`;
    UPDATE
    	leads as l
    INNER JOIN markets2users as m2u
    	ON m2u.user_id = l.counselor_id
    SET
    	l.counselor_id = 10004
    	,l.market_id = (SELECT market_id FROM markets2users where user_id = 10004)
    WHERE
    	l.lead_id = 1
    ictus==""


Bookmarks

Posting Permissions

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