Updating One Column Based on The Value of Another Column

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

Updating one column based on the value of another column

I am going to try to explain this in a simple manner as much as possible so it's easy to understand :

Let's assume, you have a table Vendor setup something like this:

create table Vendor (AccountTerms int, ulARAgeing varchar(50));

And, then we will insert some sample values for both columns in Vendor table:

insert into Vendor values
(0,'Test'),
(1,'Test1'),
(2,'Test2');

Next, we will write an update statement to update your ulARAgeing column based on the values in AccountTerms column in the same table:

update vendor 
set ulARAgeing = (CASE 
                      WHEN AccountTerms = 0 
                        THEN 'Current'
                      WHEN AccountTerms = 1
                        THEN '30 Days'
                      WHEN AccountTerms = 2
                        THEN '60 Days'
                    END);

CASE WHEN is similar to using IF..ELSE statement in most other programming languages. So, here we will be updating the existing ulARAgeing value to different string value based on the condition in the case when statement. So, for e.g. if the AccountTerms = 0 then we will update the value for ulARAgeing to `Current' and so forth.

To check if the above statement worked correctly, you just need to run the update statement above and then select from the table again:

 select * from Vendor; 

Result:

+--------------+-----------------+
| AccountTerms |   ulARAgeing    |
+--------------+-----------------+
|            0 |         Current |
|            1 |         30 Days |
|            2 |         60 Days |
+--------------+-----------------+

SQL Fiddle Demo

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 values based on equal values in another column

One option could be moving the condition inside the join and set address2 to address1 directly, as done followingly:

UPDATE     tab t1
INNER JOIN tab t2
        ON t1.ParcelID = t2.ParcelID
       AND (t1.Address = '' OR t1.Address IS NULL)
       AND NOT (t2.Address = '' OR t2.Address IS NULL)
SET t1.Address = t2.Address;

Demo here.

SQL server - Update one column based on the values of other columns

If you number of columns is fixed, then you can use a CASE statement.

Sample data

create table data
(
  A nvarchar(1),
  B nvarchar(1),
  C nvarchar(1),
  D nvarchar(1),
  E nvarchar(10)
);

insert into data (A, B, C, D) values
('Y', 'N', 'Y', 'Y'),
('N', 'N', 'N', 'Y'),
('N', 'Y', 'Y', 'N'),
('Y', 'Y', 'Y', 'N');

Solution

update d
set d.E = substring(
          case d.A when 'Y' then ',A' else '' end
        + case d.B when 'Y' then ',B' else '' end
        + case d.C when 'Y' then ',C' else '' end
        + case d.D when 'Y' then ',D' else '' end,
         2, 100)
from data d;

Result

select * from data;

A   B   C   D   E
--- --- --- --- -------
Y   N   Y   Y   A,C,D
N   N   N   Y   D
N   Y   Y   N   B,C
Y   Y   Y   N   A,B,C

SQL Fiddle

SQL query to update a record based on the max value in another column

Try this:

UPDATE t SET notes = (
   SELECT TOP 1 s.notes 
   FROM t s 
   WHERE s.item_no = t.item_no 
   ORDER BY version DESC
) WHERE version IS NULL AND notes IS NULL

Check demo

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

How to update column values depending on another column's values with CASE WHEN in SQL

You want to update the table
use

UPDATE groovers
SET profileImage = CASE WHEN gender = 'male' THEN 'imgM'
                        WHEN gender = 'female' THEN 'imgF'
                        ELSE 'default' END;

Schema (MySQL v5.7)

CREATE TABLE groovers
(
    user_id         INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(40)                                                  NOT NULL,
    firstName       VARCHAR(40)                                                  NOT NULL,
    lastName        VARCHAR(40)                                                  NOT NULL,
    gender          enum ('Male', 'Female', 'Unspecified') default 'Unspecified' NOT NULL,
    email           VARCHAR(255)                                                 NOT NULL,
    password        VARCHAR(32)                                                  NOT NULL,
    profileImage    VARCHAR(255)                                                 NOT NULL
);

INSERT INTO groovers VALUES (0,'test','test','test','male','test@test.com','a',''),
(0,'test','test','test','female','test@test.com','a',''),
(0,'test','test','test','Unspecified','test@test.com','a','');

UPDATE groovers
SET profileImage = CASE WHEN gender = 'male' THEN 'imgM'
                        WHEN gender = 'female' THEN 'imgF'
                        ELSE 'default' END;

---

Query #1

SELECT * FROM groovers;

| user_id | username | firstName | lastName | gender      | email         | password | profileImage |
| ------- | -------- | --------- | -------- | ----------- | ------------- | -------- | ------------ |
| 1       | test     | test      | test     | Male        | test@test.com | a        | imgM         |
| 2       | test     | test      | test     | Female      | test@test.com | a        | imgF         |
| 3       | test     | test      | test     | Unspecified | test@test.com | a        | default      |

---

View on DB Fiddle

---

Related Topics