CODEWITHSUNDEEP
mysqlsqlsql-update
kumaravelmathivanan
kumaravelmathivanan

Reputation: 23

How to update values in one column based on the values in another column for single update query?

Here how my tables look like: 

CREATE TABLE my_table
(
  id INT,
  user_id VARCHAR(5),
  emp_id VARCHAR(5),
  inst_id VARCHAR(5)
);

INSERT INTO my_table
VALUES
  (1, 100, 3, 0),
  (2, 200, 3, 0),
  (3, 300, 3, 0),
  (4, 400, 3, 0),
  (5, 500, 3, 0),
  (6, 600, 3, 0),
  (7, 700, 3, 0),
  (8, 800, 3, 0);


 UPDATE my_table
 SET emp_id = 0,
 user_id = inst_id
 WHERE id IN (1,3,5,6)


how to update user_id values goes to inst_id ?


finally i ought this answer

   select * from my_table where id in (1,3,5,6)

id | user_id | emp_id | inst_id
1 | 100 | 0 | 100
3 | 300 | 0 | 300
5 | 500 | 0 | 500
6 | 600 | 0 | 600

note:
i need single query.

Upvotes: 0

Views: 63

Answers (1)

Dimt
Dimt

Reputation: 2328

In order to accomplish such requirement you need to use UPDATE FROM statement:

UPDATE my_table
SET    emp_id = 0, inst_id = user_id
FROM   my_table
WHERE id IN (1,3,5,6)

Output:

id  user_id emp_id  inst_id
1   100      0       100
2   200      3       0
3   300      0       300
4   400      3       0
5   500      0       500
6   600      0       600
7   700      3       0
8   800      3       0

Upvotes: 1

Related Questions