Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    99

    Unanswered: From Select to Update

    I have a sales table that has a customer_number, salesman_number and sales data (like item, quantity, etc), there is a customer table that has the customer_number, name, address, etc. and salesman_number, with the passign of time salesmen come and go and the customer service department is in charge of modifying the customer table and assign the new salesman number to them. All the previous sales have to be reasigned to this new salesman even though it wasnt him the one that sold the items (I know, I know, thats the way the company requieres it, this is an old system). Now to my question:

    I have this select query that gives me the rows from table sales that have to be modified:

    Code:
    SELECT 	C.sales_rep,S.salesrep,S.*
    FROM	SALES AS S
    INNER JOIN customer AS C
    	ON	C.Active_Flag  = 'A'    	AND
    		C.sales_rep   != 'XXXX' 	AND
    		C.site_code    = S.site_code 	AND
    		C.cust_no      = S.cust_no	AND
    		C.cust_sffx    = S.cust_sffx	AND
    		C.division     = S.division	AND
    		C.sales_rep   != S.salesrep	
    WHERE	S.month = 2	AND
    	S.year  = 2005
    ORDER BY	C.sales_rep
    I just want to know if my approach to convert this query from an update to a select is ok:

    Code:
    UPDATE 	SALES AS S
    SET        S.salesrep = C.sales_rep
    INNER JOIN customer AS C
    	ON	C.Active_Flag  = 'A'    	AND
    		C.sales_rep   != 'XXXX' 	AND
    		C.site_code    = S.site_code 	AND
    		C.cust_no      = S.cust_no	AND
    		C.cust_sffx    = S.cust_sffx	AND
    		C.division     = S.division	AND
    		C.sales_rep   != S.salesrep	
    WHERE	S.month = 1	AND
    	S.year  = 2004
    Thanks for your help

    Luis Torres

  2. #2
    Join Date
    Aug 2004
    Posts
    99
    Made some changes to the SQL and it parsed ok, going to try it now:

    Code:
    UPDATE 	SALES 
    SET        SALES.salesrep = C.sales_rep
    FROM SALES AS S
    INNER JOIN customer AS C
    	ON	C.Active_Flag  = 'A'    	AND
    		C.sales_rep   != 'XXXX' 	AND
    		C.site_code    = SALES.site_code 	AND
    		C.cust_no      = SALES.cust_no	AND
    		C.cust_sffx    = SALES.cust_sffx	AND
    		C.division     = SALES.division	AND
    		C.sales_rep   != SALES.salesrep	
    WHERE	SALES.month = 1	AND
    	SALES.year  = 2004

  3. #3
    Join Date
    Aug 2004
    Posts
    99
    Final version, it worked perfectly

    Code:
    UPDATE 	SALES 
    SET        SALES.salesrep = C.sales_rep
    FROM SALES AS S
    INNER JOIN customer AS C
    	ON	C.Active_Flag  = 'A'    	AND
    		C.sales_rep   != 'XXXX' 	AND
    		C.site_code    = S.site_code 	AND
    		C.cust_no      = S.cust_no	AND
    		C.cust_sffx    = S.cust_sffx	AND
    		C.division     = S.division	AND
    		C.sales_rep   != S.salesrep	
    WHERE	S.month = 1	AND
    	S.year  = 2004

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Glad we could help.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Aug 2004
    Posts
    99
    Lol

  6. #6
    Join Date
    Aug 2004
    Posts
    99
    Can you believe that the last dba had a stored procedure with 2 cursors (one inside the other) to do this? The process lasted 21+ hours and it was part of our monthly load. I managed to reduce the time from 23+ hours total to 45 minutes, all the managers are jumping up and down hopefully they will offer me a permanent position here when my contract ends in May, I really enjoy working for this company.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good luck!

    I work as a consultant, and given the number of bad DBAs circulating around the workforce, its pretty common to be able to walk into a company and look like a miracle worker!
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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