Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do I populate values in same column which are null with non null values of same column on the basis of id

Example: Input Records

input_table

Output Required:

output_table

For each id, values of code1 and code2 for code3="in" should get copied to values of code1 and code2 where code3="out".

I am doing:
select distinct id from input_table where code3="in";
Applying join and taking relevant columns after joining but not getting required result.

Tags:

3

Answers


  1. 0

    You can try this SQL replacing your_table with your table name.

    UPDATE your_table AS t1
JOIN (
  SELECT id, code1, code2
  FROM your_table
  WHERE code3 = 'in'
) AS t2 ON t1.id = t2.id
SET t1.code1 = t2.code1, t1.code2 = t2.code2
WHERE t1.code3 = 'out' AND t1.code1 IS NULL AND t1.code2 IS NULL;
    Login or Signup to reply.
  2. 0 
    SELECT id,if(a.code1 = NULL,b.code1,a.code1) as code1,if(a.code2 = NULL,b.code2,a.code2) as code2,code3 FROM input_table a
LEFT JOIN (
SELECT 
id,
code1,
code2
FROM input_table
WHERE code3 = 'in'
) b ON a.id = b.id
    Login or Signup to reply.
  3. 0

    Assuming there is 1 ‘in’ row per id, you can use Max() Over() analytic function and Coalesce() to get your expected result.

    --    S a m p l e    D a t a : 
Create Table tbl ( id Int, code1 Text, code2 text, code3 text); 
Insert Into tbl 
VALUES ( 12, null, null, 'out' ),
       ( 12, null, null, 'out' ),
       ( 12, 'ABC', 'XYZ', 'in' ),
       ( 35, null, null, 'out' ),
       ( 35, null, null, 'out' ),
       ( 35, 'PQR', 'CSC', 'in' )

    --      S Q L :
Select id, 
       Coalesce( code1, Max(Case When code3 = 'in' Then code1 End) 
               Over(Partition By id) ) as code1,
       Coalesce( code2, Max(Case When code3 = 'in' Then code2 End) 
               Over(Partition By id) ) as code2, 
       code3
From   tbl

    /*
id  code1   code2   code3
--  ------  ------  -------
12  ABC     XYZ     out
12  ABC     XYZ     out
12  ABC     XYZ     in
35  PQR     CSC     out
35  PQR     CSC     out
35  PQR     CSC     in      */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search