CODEWITHSUNDEEP
mysqlphpmyadmin
redpillcoders
redpillcoders

Reputation: 93

My Sql Query to copy value inside a column

I have a problem with create a sql query. I have this situation

ColumnA    ColumnB    ColumnC

1 4 text123

1           5
1           6 
1           7

How can I make a query that fill automatically column C with a value of row 1/4?

I want: if columnA Value=1 and ColumnB Value=5 and ColumnC Value=NULL than take the value of ColumnC row where A=1 and B=4 and fill ColumnC Value with that like this

Column A Column B Column C

1 4 text123

1        5         text123
1        6 
1        7

I need this for other entries also like columnA = 2 columnA = 3.

Upvotes: 0

Views: 32

Answers (1)

xQbert
xQbert

Reputation: 35323

Pretty easy to do with user variables and a case statement:

Note this logic simulates the Lag/Lead analytic/window functions found in other RDBM Systems. MySQL does not presently support window/analytical functions.

Working DEMO:

SELECT ColumnA
     , ColumnB
     , case when columnC is null then @PriorVal 
            else @PriorVal:=ColumnC end as ColumnC
FROM YourTableName
CROSS JOIN (SELECT @PriorVal:='') z
ORDER BY ColumnA, ColumnB;

We use uservariable @PriorVal to keep track of C values which are not null and then substitute that value for the null values in C based on the order columnA, ColumnB. Each time columnC has a non-null value, the userVariable @PriorValue is set to C and that value displayed. Anytime the columnC has a NULL value, the stored PriorVal userVariable is displayed instead. This approach is heavily dependent upon the order by.

@PriorVal is initialized in cross join derived table (z) above This method makes it easier to use in conjunction with PHP as two separate statements don't need to be passed.

Using my sample data set:

Insert into foo_47514588 values 
   (1,4,'text123')
  ,(1,5,NULL)
  ,(1,6,NULL)
  ,(1,7,NULL)
  ,(1,8,'text2')
  ,(1,10,NULL)
  ,(2,1,'Text3');

It achieves the Below: 

+----+---------+---------+---------+
|    | ColumnA | ColumnB | COlumnC |
+----+---------+---------+---------+
|  1 |       1 |       4 | text123 |
|  2 |       1 |       5 | text123 |  <--Added by case statement
|  3 |       1 |       6 | text123 |  <--Added by case statement
|  4 |       1 |       7 | text123 |  <--Added by case statement
|  5 |       1 |       8 | text2   |
|  6 |       1 |      10 | text2   |  <--Added by case statement
|  7 |       2 |       1 | Text3   |
+----+---------+---------+---------+

Upvotes: 1

Related Questions