My Sql Query to copy value inside a column

Question

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.

so is the order by col a, column b, such that C's values will always be preceding columnC value if null? — xQbert
– xQbert, Commented Nov 27, 2017 at 16:43
Article 1 (ID ColumnA) has 4 languages options EN/DE/FR/IT (ColumnB). only in the first one (EN is some text (ColumnC). If another languages data empty for that article than fill the empty fields (DE/FR/IT) with the value from EN. And the same i need for the next article ID 2 and article ID 3 — redpillcoders
– redpillcoders, Commented Nov 27, 2017 at 16:53

xQbert · Accepted Answer · 2017-11-27 17:01:06Z

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   |
+----+---------+---------+---------+

Collectives™ on Stack Overflow

My Sql Query to copy value inside a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related