how can I update a top 1 records in teradata

Question

I am trying to update the first record clubcode + campaigncode + region based on their tenureyear. (Tenureyears is causing the duplicate records but I need them)

This works in T-SQL but not Teradata

update MYTable
set qtyupdate =(SELECT quantity
  FROM codes
  WHERE MYTable.clubcode=codes.clubcode 
        AND MYTable.campaigncode=codes.campaigncode
        and MYTable.region=codes.region)
where identity_column in (select top 1 x.identity_column from MYTable X where x.qtyupdate = MMYTable.qtyupdate order by x.tenureyears)

It is only updating 1 records on the table and I also get error 3706 cannot use order by in subqueries.

Fred · Accepted Answer · 2019-08-07T23:23:32.947

If your example worked, it would only update 1 row (even in T-SQL), so your question is not entirely clear. Perhaps something like this?

update MYTable q
set qtyupdate =(
    SELECT  c.quantity
      FROM  codes c
      WHERE q.clubcode=c.clubcode 
            AND q.campaigncode=c.campaigncode
            and q.region=c.region)
where   identity_column in
 (
    select  z.identity_column 
    from    (
         select x.identity_column, x.qtyupdate,
         rank() over (order by x.tenureyears) as rnk 
        from    MYTable X 
    ) z
    where   z.rnk=1
    and z.qtyupdate = q.qtyupdate 
);

how can I update a top 1 records in teradata

1 Answers1