SQL Update Set One Column to Be Equal to a Value in a Related Table Referenced by a Different Column

SQL UPDATE SET one column to be equal to a value in a related table referenced by a different column?

update q
set q.QuestionID = a.QuestionID
from QuestionTrackings q
inner join QuestionAnswers a
on q.AnswerID = a.AnswerID
where q.QuestionID is null -- and other conditions you might want

I recommend to check what the result set to update is before running the update (same query, just with a select):

select *
from QuestionTrackings q
inner join QuestionAnswers a
on q.AnswerID = a.AnswerID
where q.QuestionID is null -- and other conditions you might want

Particularly whether each answer id has definitely only 1 associated question id.

SQL UPDATE SET a column to be equal to a certain value in a related table that referenced by a different column?

It sounds like a join and update:

update customers c join
       call_card cc
       on c.location_id = cc.location_id and c.visit_id = cc.visit_id
    set c.card_id = cc.card_id;

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 values from one column in same table to another in SQL Server

This works for me

select * from stuff

update stuff
set TYPE1 = TYPE2
where TYPE1 is null;

update stuff
set TYPE1 = TYPE2
where TYPE1 ='Blank';

select * from stuff

SQL set values of one column equal to values of another column in the same table

Sounds like you're working in just one table so something like this:

update your_table
set B = A
where B is null

How to update a column with data from another column in SQL Server?

You can try this as shown below.

UPDATE a 
    SET a.UserName = b.Kwd
    FROM test_table1 a INNER JOIN test_table1 b ON a.Id= b.Id

You can also try the following query.

update test_table1 
   set test_table1.UserName = B.Kwd
  from test_table1 B

You can follow the link Inner join update in SQL Server

Here is an example with sample data.

create table test_table1 (PelID int, Kwd varchar(10), UserName varchar(10))
insert into test_table1 Values (1, 'A', 'B'), (2, 'K', 'P'), (3, 'N', 'S'), (4, 'G', 'H'), (5, 'T', 'F')

Select * from test_table1

UPDATE a 
    SET a.UserName = b.Kwd
    FROM test_table1 a INNER JOIN test_table1 b ON a.PelID = b.PelID

Select * from test_table1

update test_table1 
   set test_table1.UserName = B.Kwd
  from test_table1 B

Select * from test_table1

This output can be checked on the link

Update one column based on three other column which have same value in another table

You can solve it with an additional table or CTE to identify duplicate or genuine records. A GROUP BY or a join can be used to check the duplicates.

Please note that you also need to check for nulls in equations if the columns are nullable.

with GenuineData as (
   select
      min(t2.Id) id
   from
      Table2 t2
   group by
      t2.Column1,
      t2.Column2,
      t2.Column3
   having
      count(*) = 1
)
update t1 set
   t1.Table2Id = t2.Id
from
   Table1 t1
   join Table2 t2 on t2.Column1 = t1.Column1 and t2.Column2 = t1.Column2 and t2.Column3 = t1.Column3
   join GenuineData gd on gd.id = t2.Id

If the columns are nullable, you can use ISNULL() in the equations, or for better performance, an extended criteria for joining Table2 as below:

   join Table2 t2 on
      (t2.Column1 = t1.Column1 or (t1.Column1 is null and t2.Column1 is null))
      and (t2.Column2 = t1.Column2 or (t1.Column2 is null and t2.Column2 is null))
      and (t2.Column3 = t1.Column3 or (t1.Column3 is null and t2.Column3 is null))

---

Related Topics