Update Columns Values with Column of Another Table Based on Condition

update columns values with column of another table based on condition

Something like this should do it :

UPDATE table1 
   SET table1.Price = table2.price 
   FROM table1  INNER JOIN  table2 ON table1.id = table2.id

You can also try this:

UPDATE table1 
   SET price=(SELECT price FROM table2 WHERE table1.id=table2.id);

Update column with values from another table if ID exists in another table

Use an INNER JOIN not a subquery. This will implicitly filter to only rows where the related row is found:

UPDATE T1
SET [Value] = T2.Value
FROM dbo.Table1 T1
     JOIN dbo.Table2 T2 ON T1.ID = T2.ID;

db<>fiddle

How to update column in a table from another table based on condition?

Why to use sub-query when you can do that directly?

UPDATE st
  SET st.school_code = sc.school_id 
FROM master.student AS st
  JOIN Master.school AS sc
ON st.school_code = sc.school_code
WHERE sc.year=x
  AND st.year=x;

For more info See UPDATE (Transact-SQL)

update column in one table based on condition in another table

UPDATE a SET Remarks=b.Remarks || ',$A' FROM inventory a JOIN priceguide_inventory b ON a.Lot_ID = b.Lot_ID WHERE a.condition='New' 

Update Column based on condition and create a new row in another table for every update

I would populate my audit table before the update, so that I can use the same conditions for populating the audit as I would for updating, You could wrap the two operations in a transaction to ensure they occur at the same time. e.g

BEGIN TRAN UpdateAndRecord

INSERT INTO AuditTable
column1
,column2
)
(Select Column1
,Column2
FROM [mainTable] 
INNER JOIN [otherTable]
ON [mainTable].[foreignKey]=ad.[primaryKey]
Where [mainTable].[columnA]<>[otherTable].[columnB]
)

UPDATE [mainTable]
SET [mainTable].[columnA]=[otherTable].[columnB]
FROM [mainTable] 
INNER JOIN [otherTable]
ON [mainTable].[foreignKey]=ad.[primaryKey]
Where [mainTable].[columnA]<>[otherTable].[columnB]

COMMIT TRAN UpdateAndRecord

Update column based on select from another table

You can join an UPDATE statement with a FROM clause. In your case:

UPDATE names
SET min_price = q.min_price
FROM (
  SELECT name, MIN(price) as min_price
  FROM historical
  GROUP BY name
) as q
WHERE name = q.name

The alternative would be a subselect, but that would also try to update all names, not only those that exist in historical:

UPDATE names
SET min_price = (SELECT MIN(price) FROM historical WHERE historical.name = names.name)

Update column with part of the data from column of another table in another database with intermediate tables

I have found the solution. The problem was quite complex but the answer turned out to be simple after thinking it through.

My update statement that almost worked was lacking an additional condition in select statement and where clause.

update DB1.Tab1 as T1,
    (select 
        col3, T1.col2 as T1c2
    from
        DB2.Tab2 as T2
    inner join DB2.Tab4 as T4 ON T2.col5 = T4.col8
    inner join DB1.Tab3 as T3 ON T4.col9 = T3.col6
    inner join DB1.Tab1 as T1 ON T3.col7 = T1.col2
    where
        T1.col1 is null
            and T1.col2 is not null
    group by col3
    having count(*) = 1) as T2d 
set 
    T1.col1 = T2d.col3
where
    T1.col1 is null
        and T1.col2 = T1c2;

The solution comes down to selecting yet another column from the table to be updated (T1.col2), and specifically the values for which T1.col1 should be updated, then compare each T1.col2 with the previously selected ones.

However the mechanism behind it isn't clear to me, specifically why update statement without this edit would update all fields with only one value, so comments are still appreciated.

Update from another table with multiple condition

A common column is needed from TABLE_C with the others for matching condition, that seems to be mat_id with TABLE_B.

A subquery needed, which should include the NULL vs. NOT NULL cases ( NVL(a.ch_dt, a.mod_dat) ) , to be used to bring the counterpart value for ch_dd_dt column, and after EXISTS clause.

Therefore I constructed such a query :

UPDATE TABLE_C c
   SET c.ch_dd_dt =
       (SELECT NVL(a.ch_dt, a.mod_dat)
          FROM TABLE_A a
          JOIN TABLE_B b
            ON b.cl_no = a.cl_no
         WHERE a.ch_dt IS NULL
           AND b.mat_id = c.mat_id)
 WHERE EXISTS (SELECT NVL(a.ch_dt, a.mod_dat)
                 FROM TABLE_A a
                 JOIN TABLE_B b
                   ON b.cl_no = a.cl_no
                WHERE a.ch_dt IS NULL
                  AND b.mat_id = c.mat_id)

which only updates the row of TABLE_C with mat_id = '04'

Demo

Alternatively, you can use a MERGE Statement including MATCHED case only :

MERGE INTO TABLE_C c
  USING
       (SELECT NVL(a.ch_dt, a.mod_dat) AS ch_dt, b.mat_id
          FROM TABLE_A a
          JOIN TABLE_B b
            ON b.cl_no = a.cl_no
         WHERE a.ch_dt IS NULL) ab
           ON ( ab.mat_id = c.mat_id)
 WHEN MATCHED THEN UPDATE SET c.ch_dd_dt = ab.ch_dt  

Demo

Update a table with data from other table with multiple conditions?

I suspect that you really want EXISTS -- that is to set all values in table A, with 1 if there is a non-NULL matching event. That would be:

UPDATE A
    SET NEWCOLUMN = (CASE WHEN EXISTS (SELECT 1
                                       FROM B
                                       WHERE b.ARTICLENUMBER = a.ARTICLENUMBER AND
                                             b.EVENT IS NOT NULL
                                        )
                          THEN 1 ELSE 0
                      END);

Note that this updates all rows in A -- even those with no matching article in B. As I say, I think this is what you want to do, although it is not exactly how your question is phrased. Your question does not specify what to do for ARTICLENUMBERs that are not in B.

---

Related Topics