Update query with row_num = 1

Transact-SQL
1

I need to update only the row where row number = 1 but the query below is updating all the rows.

Select Query to pick the row number 1

(select *
FROM
(select (ROW_NUMBER() OVER (PARTITION BY (eml_ad) ORDER BY load_dw_audt_ts DESC ) )AS row_num,*
from dev_tables.dcs_cust_eml_ad e
where dcs_cust_acct_id in
(select dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
where dcs_cust_acct_id not in
(select distinct dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
where cust_eml_ad_end_ts is null
)) ) where row_num = 1 )

----->
Update query is updating all the rows

UPDATE dev_TABLES.DCS_CUST_EML_AD
SET CUST_EML_AD_end_TS = NULL,
UPDT_DW_AUDT_TS = getdate(),
dw_file_id = '-6'
from (select *
FROM
(select (ROW_NUMBER() OVER (PARTITION BY (eml_ad) ORDER BY load_dw_audt_ts DESC ) )AS row_num,*
from dev_tables.dcs_cust_eml_ad e
where dcs_cust_acct_id in
(select dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
where dcs_cust_acct_id not in
(select distinct dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
where cust_eml_ad_end_ts is null
)) ) where row_num = 1 ) AS S
where dev_TABLES.DCS_CUST_EML_AD.EML_AD = S.EML_AD and dev_TABLES.DCS_CUST_EML_AD.CUST_EML_AD_END_TS IS NOT NULL

2

Use your first query and just change the SELECT to DELETE. Note, you missed the alias in the derived table

DELETE	D
FROM
(
	select (ROW_NUMBER() OVER (PARTITION BY (eml_ad) ORDER BY load_dw_audt_ts DESC ) )AS row_num,*
	from dev_tables.dcs_cust_eml_ad e
	where dcs_cust_acct_id in
		(select dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
		where dcs_cust_acct_id not in
			(select distinct dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
			where cust_eml_ad_end_ts is null
			)
		) 
) AS D
where	row_num = 1
3

In the inner query, you specify "ORDER BY load_dw_audt_ts DESC", but you only match on the EML_AD for the UPDATE. Shouldn't you match on both EML_AD and load_dw_audt_ts so that you only update the single row that has the max load_dw_audt_ts value?

4

Hi Scott, Appreciate your time. You are right but its not letting me use aggregate function with update query

5

i have been suggested to create a temp table to over come this but i am a data analyst with out rights to create a temp table. So i wanted to figure out if a single query can do that updates as well.

6

I think a cte would be the most efficient way, something like this:

;WITH cte_find_rows_to_update AS (
    select * 
    from (
    select (ROW_NUMBER() OVER (PARTITION BY (eml_ad) ORDER BY load_dw_audt_ts DESC ) )AS row_num,*
    from dev_tables.dcs_cust_eml_ad e
    where dcs_cust_acct_id in
    (select dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
    where dcs_cust_acct_id not in
    (select distinct dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
    where cust_eml_ad_end_ts is null
    ))
    ) as r where row_num = 1
) 
UPDATE cte_find_rows_to_update
SET CUST_EML_AD_end_TS = NULL,
    UPDT_DW_AUDT_TS = getdate(),
    dw_file_id = '-6'
where 
    CUST_EML_AD_END_TS IS NOT NULL
7

Thanks Scott for the suggestion. But i keep running into syntax error Invalid operation: syntax error at or near "UPDATE" .

I was trying to run this
WITH cte_find_rows_to_update AS (
select *
from (
select (ROW_NUMBER() OVER (PARTITION BY (eml_ad) ORDER BY load_dw_audt_ts DESC ) )AS row_num,*
from dev_tables.dcs_cust_eml_ad e
where dcs_cust_acct_id in
(select dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
where dcs_cust_acct_id not in
(select distinct dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
where cust_eml_ad_end_ts is null
))
) as r where row_num = 1
)
UPDATE cte_find_rows_to_update
SET CUST_EML_AD_END_TS = NULL,
UPDT_DW_AUDT_TS = getdate(),
dw_file_id = '-6'
where CUST_EML_AD_END_TS IS NOT NULL

8

Is this for SQL Server or some other dbms, like MySQL?

9

Unfortunately it is for Redshift Amazon database

10

Ah, OK, I knew something was up.

Then we'd need to revert to your earlier syntax, but add a condition to the WHERE. Hopefully this works! For "standard" SQL, you'd need an alias on the subquery (which I've commented out, "AS r"). It seems as if Redshift doesn't require it, though. If you get a synax error about an error near "where", uncomment the "as r".

UPDATE dev_TABLES.DCS_CUST_EML_AD
SET CUST_EML_AD_end_TS = NULL,
    UPDT_DW_AUDT_TS = getdate(),
    dw_file_id = '-6'
from (select *
    FROM
    (select (ROW_NUMBER() OVER (PARTITION BY (eml_ad) ORDER BY load_dw_audt_ts DESC ) )AS row_num,*
    from dev_tables.dcs_cust_eml_ad e
    where dcs_cust_acct_id in
    (select dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
    where dcs_cust_acct_id not in
    (select distinct dcs_cust_acct_id from dev_tables.dcs_cust_eml_ad
    where cust_eml_ad_end_ts is null
    )) ) /*AS r*/ where row_num = 1 
) AS S
where dev_TABLES.DCS_CUST_EML_AD.EML_AD = S.EML_AD and 
    dev_TABLES.DCS_CUST_EML_AD.load_dw_audt_ts = S.load_dw_audt_ts and /*<<--<<*/
    dev_TABLES.DCS_CUST_EML_AD.CUST_EML_AD_END_TS IS NOT NULL
11

It worked! Brilliant! Amazingly helpful ! Thank you so much!

12

Great, glad it helped!

I used the ;WITH cte because in SQL Server, that would have been more efficient. But not all database systems support it :-(.

13

I agree, that makes sense. I really appreciate the prompt responses. Thank you!