update column table(table has duplicates) with values of another column table

sql update column with value from another column in the same table
sql update column with value from another table
sql set column value based on another column
mysql update
select distinct on one column with multiple columns returned
mysql find duplicate values multiple columns
update one column from another column in different table sql server
sql distinct multiple columns

I need to update a column in a table(table has duplicates) with values of another table . I tried couple of codes but it gives me error

ERROR: Update canceled: attempt to update a target row with values from multiple join rows

Here is my code:

UPDATE SERGIU_BI_CCM_AGG_MTH t1
  SET t1.CURRENT_SEC = foo.CURRENT_SEC
  FROM (
  SELECT t1a.sub_id, t1a.CURRENT_BRAND, t2.CURRENT_SEC, t2.from_date,      
  t2.to_date
  FROM SERGIU_BI_CCM_AGG_MTH t1a
     LEFT JOIN BI_CCM_BASE t2
     ON t1a.sub_id = t2.sub_id and t1a.CURRENT_BRAND = t2.CURRENT_BRAND
  )
  foo 
WHERE t1.sub_id = foo.sub_id and t1.CURRENT_BRAND = foo.CURRENT_BRAND    
 and  t1.agg_mth between to_char(foo.from_date,'YYYYMM') and   
 to_char(foo.to_date-1,'YYYYMM');

Can anyone help me?

The duplicates are causing the error, lets get unique record for each sub_id and CURRENT_BRAND using row_number().

try below query

UPDATE SERGIU_BI_CCM_AGG_MTH t1
SET t1.CURRENT_SEC = foo.CURRENT_SEC
FROM (
        SELECT t1a.sub_id
            , t1a.CURRENT_BRAND
            , t2.CURRENT_SEC
            , t2.from_date
            , t2.to_date
            , row_number() over (partition by t1a.sub_id,CURRENT_BRAND order by from_date desc) as rn
        FROM SERGIU_BI_CCM_AGG_MTH t1a
        LEFT JOIN BI_CCM_BASE t2 ON t1a.sub_id = t2.sub_id 
            and t1a.CURRENT_BRAND = t2.CURRENT_BRAND
      )foo 
WHERE t1.sub_id = foo.sub_id 
    and t1.CURRENT_BRAND = foo.CURRENT_BRAND    
    and t1.agg_mth between to_char(foo.from_date,'YYYYMM') and to_char(foo.to_date-1,'YYYYMM');
    and foo.rn = 1

How can I use SQL to update multiple columns?, How do you update a column with a value from another table? The query works but i don't see any changes in the table SERGIU_BI_CCM_AGG_MTH – joraCrd Oct 18 '19 at 7:43 try to select first before doing the update. if there's no result on the select, it means, there's no matching record based on your criteria. btw, what's the datatype of yoru to_date and from_date fields?

Use SQL UPDATE to Query and Modify Data, How do you update one column to another column in the same table? I need to update HolidayCity column from the first table with the values in CityID column from the second table for each UserID, but only those records, where ValidTo IS NULL, so that the resulting table Users1 would be:

You need the following query.

    UPDATE SERGIU_BI_CCM_AGG_MTH t1
    SET t1.CURRENT_SEC = (SELECT  t2.CURRENT_SEC   
    FROM SERGIU_BI_CCM_AGG_MTH t1a
    LEFT JOIN BI_CCM_BASE t2
    ON t1a.sub_id = t2.sub_id and t1a.CURRENT_BRAND = t2.CURRENT_BRAND
WHERE t1.sub_id = foo.sub_id and t1.CURRENT_BRAND = foo.CURRENT_BRAND    
      and  t1.agg_mth between to_char(foo.from_date,'YYYYMM') and   
      to_char(foo.to_date-1,'YYYYMM'))                 

How To Find Duplicate Values in MySQL, How do you update the same column with different values in SQL? First, specify the table name where you want to update the data after UPDATE clause. Second, list the columns whose values you want to change in the SET clause. If you update values in multiple columns, you use a comma (,) to separate each pair of column and value. The columns that are not on the list retain their original values.

How to Use The MySQL REPLACE Statement to Insert or Update, How do I change the value of a column in a select query? Duplicate the column named columnName to the table table. The values and type for the column newColumnName are copied from column columnName. Example. Duplicate the column "a" to a column named "copied column" in the table ({[a = 1, b = 2], [a = 3, b = 4]}).

SQL Server SELECT DISTINCT, The SQL UPDATE statement is used to modify column values within a I've decided to create an example table for use within the database to Unique Key Violation – you're trying to update a value which causes a duplicate key value. how to update's column values based on a match to another table. The SOURCE table has duplicates. So when record 1 is inserted into TARGET table and then record 3 which is a duplicated, shouldn't it be skipped since record 1 is already inserted?

MySQL UPDATE, In the contacts table, we have some rows that have duplicate values in the first_name , last_name , and email columns. Let's learn how to find them. Update one column of a table with data from another table I wish to use an UPDATE query to change data in one column of a table to the values from another table. It is simple enough to do so record by record but can I do so using something similar to the INSERT INTO

Comments
  • i will try an let you know thank you
  • ERROR: Column reference "SUB_ID" is ambiguous this message shows in netezza
  • The query works but i don't see any changes in the table SERGIU_BI_CCM_AGG_MTH
  • try to select first before doing the update. if there's no result on the select, it means, there's no matching record based on your criteria. btw, what's the datatype of yoru to_date and from_date fields?
  • YEs you are right , i checked and the are Null values in the column, i will populate with sub_id that has values other values to see if it updated
  • Netezza don't support correlated queries :) , thank you