0

My database is in MySQL

I have a table, let's say of 4 columns. I would like to know if it's possible, and how to implement the following: fill the 4th column according to the value of the column 2 and column 3

In Excel I have a formula, let's give an example: if column2 value is set to "grey" and column3 value is set to "car", then column 4 value should be set to "super"

I just say this as an example.

My real formula in Excel looks like this: =IF(K4=4;"Maximal";IF(K4>4;"Maximal";IF(K4=3;"Important";IF(K4>3;"Important";IF(K4=2;"Limited";IF(K4>2;"Limited";IF(K4=1;"Forgettable";IF(K4>1;"Forgettable";"error"))))))))

However I want to do it in SQL. I was thinking of creating my table until the column 3, set column 4 to NULL or empty, then open a GUI written in Java and maybe there do a piece of code to automatically fill the column 4 according to what is in column 2 and column 3 (these values will be choosable via Choicelist). But if there is a way to do it directly in SQL, I am interested

Thx a lot in advance for your help.

regards

underscore_d
  • 6,309
  • 3
  • 38
  • 64
tom
  • 55
  • 1
  • 9
  • What you probably want is to use a `Select` query with a `Case` statement: http://stackoverflow.com/a/14189418/3047101 – Stephan Jul 20 '16 at 12:13
  • "fill a column" - please clarify whether this column is meant to physically exist in the database, or whether it's just a presentation element. – underscore_d Jul 20 '16 at 20:16

5 Answers5

2

Yes. you can easily update your NULL-values according to some requirements for the other values in other columns of a particular row with the Update statement

UPDATE <tablename> 
SET <column> = 'value'
WHERE <condition>

The only drawback here might be that you have to create an update statement for each of the combinations of your values in column2 and column3. (however, it's not much work for your amount of conditions).

I created an example (demo):

  1. Creating a table in SQL according to your example could look like this,I used a temporary one for the sake of an example:
CREATE GLOBAL TEMPORARY TABLE demoTable (
 "Col1" VARCHAR2(50 BYTE) NOT NULL,
 "Col2" VARCHAR2(50 BYTE) NOT NULL,
 "Col3" VARCHAR2(50 BYTE) NOT NULL,
 "Col4" VARCHAR2(50 BYTE) DEFAULT NULL
)
ON COMMIT PRESERVE ROWS
  1. I also inserted some dummy data:

INSERT INTO demoTable VALUES ('Charles', 'grey', 'car', NULL); INSERT INTO demoTable VALUES ('Alice', 'grey', 'bike', NULL); INSERT INTO demoTable VALUES ('Bob', 'red', 'car', NULL);

The result:

enter image description here

  1. Now, create the update statements like this, for example: 
UPDATE demoTable dt 
SET dt."Col4" = 'super'
WHERE dt."Col2" = 'grey' AND dt."Col3" = 'car';

The result

enter image description here

Grace
  • 46
  • 3
1

You can try like this; 

select * from mytable

COL1 COL2                
---- --------------------
0    -                   
1    -                   
2    -                   
3    -                   
4    -                   

  4 record(s) selected.


update mytable Set Col2 =
Case
When Col1<1 Then 'error' 
When Col1=1 Then 'Forget' 
When Col1=2 Then 'Limited' 
When Col1=3 Then 'Important' 
When Col1=4 Then 'Maximal' 
End"


select * from mytable"

COL1 COL2                
---- --------------------
0    Error                 
1    Forget                 
2    Limited             
3    Important           
4    Maximal             

  4 record(s) selected.
Mustafa DOGRU
  • 3,994
  • 1
  • 16
  • 24
0

You can create a sql function, lets say udfGetColumn4Value taking in the column2, column3 as parameters to it and return a value.

Now you can run a select column2, column3, udfGetColumn4Value(column2, column3) from table or a query as desired. Hope this helps.

Prashant Majhwar
  • 170
  • 1
  • 9
0

You were not very precise regarding which DBMS you're using. And also about the exact logic behind using your two columns.

Still here comes a probable SQL-Server solution, where I have taken one statement using CASE WHEN with your example and concatenated your two columns col2 and col3 (you can apply your further logic of here) otherwise:

UPDATE TableName
SET Col4 = CASE WHEN col2 = 'red' AND col3 = 'car' THEN 'super' ELSE col2 + col3 END;

You should replace col2 + col3 with your further logic.

Chaos Legion
  • 2,730
  • 1
  • 15
  • 14
0

Seems that a simple UPDATE-Query could address your problem:

update things set result = "super" where thing = "car" and color = "grey";

The where-clause does what you desire to do by saying

fill the column 4 according to what is in column 2 and column 3

I created a test table here on turorialspoint, there you can check if it fits your needs.

danny
  • 358
  • 2
  • 14