locked
UPDATE from a select statement RRS feed

  • Question

  • I am trying to do an update from a select statement which filters out what i need to update.
    UPDATE FROM geprs_price..T_PRC_LEAD_XCLD
    
    SET END_DT = GETDATE(),
    	STAT_CD = 'H', 
    	UPDATE_TS = GETDATE(),
    	UPDATE_BY = USER_NAME()
    
    WHERE (SELECT ldxl.EM_ITEM_NUM 
    		, ldxl.LEAD AS BLOCK_LEAD
    				, ref.EM_ITEM_NUM
    				, ref.SELL_DSCR, ref.DITM_ACTIVE_FLG 
    				, ldxl.EFF_DT
    				, ldxl.END_DT
    				, ldxl.STAT_CD 
    		FROM reference..T_IW_EM_ITEM ref
    			INNER JOIN geprs_price..T_PRC_LEAD_XCLD ldxl 
    			ON ldxl.EM_ITEM_NUM = ref.EM_ITEM_NUM
    
    		WHERE DITM_ACTIVE_FLG= 'N'
    		and GETDATE() BETWEEN ldxl.EFF_DT AND ldxl.END_DT
    		and ldxl.STAT_CD = 'C' )
    
    Tuesday, September 14, 2010 9:20 PM

Answers

  • In SQL Server 2005 and up:

    ;with cte as (select to filter records we want to update)

    update cte set End_Dt  = getdate(), etc.

    ------------------------------------------------

    In SQL Server 2000 you can do

    update myTable set ...

    where exists (select 1 from (my complex select) T where myTable.PKField = T.PKField)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by siera_gld Tuesday, September 14, 2010 10:06 PM
    Tuesday, September 14, 2010 9:31 PM
  • You need a predicate in the WHERE clause. Here is my guess.

    You need a predicate in the WHERE clause. Here is my guess. 

     

     

    UPDATE Geprs_Price..t_prc_lead_xcld

       SET end_date = CURRENT_TIMESTAMP,

           stat_code = 'H', 

           update_timestamp= CURRENT_TIMESTAMP,

           update_user = USER_NAME()

     WHERE EXISTS

          (SELECT LDXL.em_item_num, LDXL.lead AS block_lead,

                  REF.em_item_num, REF.sell_dscr, REF.ditm_active_flg, 

                  LDXL.eff_dt, LDXL.end_date, LDXL.stat_code

             FROM Reference..t_iw_em_item AS REF

                  Geprs_Price..t_prc_lead_xcld AS LDXL 

            WHERE LDXL.em_item_num = REF.em_item_num

              AND ditm_active_flg = 'N'

              AND CURRENT_TIMESTAMP BETWEEN LDXL.eff_dt AND LDXL.end_date

              AND LDXL.stat_code = 'C');

    I see you write with flags, do not follow ISO-11179 naming rules, and keep s=audit data in the table being audited. These are not good things. 


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    • Marked as answer by Kalman Toth Thursday, November 18, 2010 9:45 PM
    Tuesday, September 14, 2010 9:47 PM

All replies

  • Hi, you must have a boolean condition !

    Update from <Table> set .... where <boolean condition>

    In this case (select ....) is not a boolean condition

     


     

    Best regards

    Tuesday, September 14, 2010 9:27 PM
  • Why you want to select all these records, if u can update that without selecting records and just where condition

     

     

     

    TRY THIS

     

     

    UPDATE A

    SET

    ALL YOUR COLUMNS TO SET

    FROM

    TABLEA AS A

    INNER JOIN

    TABLEB AS B

    WHERE (YOUR CONDITION)

    DITM_CTIVE_FLG= 'N'
    		and GETDATE() BETWEEN ldxl.EFF_DT AND ldxl.END_DT
    		and ldxl.STAT_CD = 'C'
    Tuesday, September 14, 2010 9:29 PM
  • In SQL Server 2005 and up:

    ;with cte as (select to filter records we want to update)

    update cte set End_Dt  = getdate(), etc.

    ------------------------------------------------

    In SQL Server 2000 you can do

    update myTable set ...

    where exists (select 1 from (my complex select) T where myTable.PKField = T.PKField)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by siera_gld Tuesday, September 14, 2010 10:06 PM
    Tuesday, September 14, 2010 9:31 PM
  • You need a predicate in the WHERE clause. Here is my guess.

    You need a predicate in the WHERE clause. Here is my guess. 

     

     

    UPDATE Geprs_Price..t_prc_lead_xcld

       SET end_date = CURRENT_TIMESTAMP,

           stat_code = 'H', 

           update_timestamp= CURRENT_TIMESTAMP,

           update_user = USER_NAME()

     WHERE EXISTS

          (SELECT LDXL.em_item_num, LDXL.lead AS block_lead,

                  REF.em_item_num, REF.sell_dscr, REF.ditm_active_flg, 

                  LDXL.eff_dt, LDXL.end_date, LDXL.stat_code

             FROM Reference..t_iw_em_item AS REF

                  Geprs_Price..t_prc_lead_xcld AS LDXL 

            WHERE LDXL.em_item_num = REF.em_item_num

              AND ditm_active_flg = 'N'

              AND CURRENT_TIMESTAMP BETWEEN LDXL.eff_dt AND LDXL.end_date

              AND LDXL.stat_code = 'C');

    I see you write with flags, do not follow ISO-11179 naming rules, and keep s=audit data in the table being audited. These are not good things. 


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    • Marked as answer by Kalman Toth Thursday, November 18, 2010 9:45 PM
    Tuesday, September 14, 2010 9:47 PM
  • Try this query :

    UPDATE FROM geprs_price..T_PRC_LEAD_XCLD

    SET END_DT = GETDATE(),
    STAT_CD = 'H',
    UPDATE_TS = GETDATE(),
    UPDATE_BY = USER_NAME()

    WHERE EM_ITEM_NUM
     in  (SELECT ldxl.EM_ITEM_NUM
    FROM reference..T_IW_EM_ITEM ref
    INNER JOIN geprs_price..T_PRC_LEAD_XCLD ldxl
    ON ldxl.EM_ITEM_NUM = ref.EM_ITEM_NUM

    WHERE DITM_ACTIVE_FLG= 'N'
    and GETDATE() BETWEEN ldxl.EFF_DT AND ldxl.END_DT
    and ldxl.STAT_CD = 'C' )


    Best regards
    Tuesday, September 14, 2010 10:00 PM