How to insert data into a specific column without NULL in other columns?

Question

I have a table ("table1") with 3 columns called col1, col2 and col3 (each one is VARCHAR) with 4 values, as shown below:

col1 col2 col3 datA1 datB1 datC1 datA2 

I need the ability to add data at any time to any column that does not affect the others. A very popular code on the Internet is that (let's say we need to add data only to col2 and col3 columns):

 INSERT INTO table1 (col2, col3) VALUES ('datB2', 'datC2'); 

But it adds new lines as shown below:

 col1 col2 col3 datA1 datB1 datC1 datA2 NULL datB2 datC2 

I really need to fill in a row, starting with the value "datA2" in the column "col1", with the new values ​​and get the table as shown below:

 col1 col2 col3 datA1 datB1 datC1 datA2 datB2 datC2 

If someone can help me, I will be very grateful !!! Thank you Arseny.

---

Update: The table has 3 columns and the answers of each column for a certain type of value (for example: name, color, size). I only need the ability to add new values ​​at any time in a particular column and have them without Null and new rows, if there is a free cell before that.

Answer 1

I found a solution (a chain of logical operations):

1) CHECK , if there is a cell (in the target column) with the values "" or NULL .

2) IF it has one of them, and then rewrites FIRST, keeping the values ​​of other cells in this line in their places (presumably we use UPDATE )))).

3) ELSE just add a new row with all NULL to another cell in the row.

If we want to add several values ​​to different columns at the same time, we can prepare our queries for all of them, and then execute them at the same time (sorry for the tautology).

If we need to add several values ​​to one column in one query, we can prepare it using loops (repeating items 1 and 2 (or, optionally, 3).

Answer 2

For a table structure with two rows of data:

 key value -------------------- team accounts manager jeff 

Every time you want to change a value, you need to check if it is there (for updating) or not (for pasting). So, to change the value of the manager property:

 if exists(select * from keyValues where key = 'manager') update keyValues set value = 'mike' where key = 'manager' else insert into keyValues ('manager', 'mike') 

Answer 3

You will need to use the UPDATE if you want to add data to an existing row. For example, for example:

 UPDATE table1 SET col2 = 'data5' col3 = 'data6' FROM table1 WHERE col1 = 'data4' 

And it looks like the root of your problem is the poor database design, but this query just shows you how to add data to an existing row.

Answer 4

Use this:

 INSERT INTO table1 (col2, col3) VALUES ('datB2', 'datC2') WHERE col1 = datA2; 

Answer 5

 UPDATE table1 SET col2 = dataB2, col3 = dataC2 WHERE col1 = dataA2; 

This may serve your purpose :)

Answer 6

Use this if null then insert a null value.

 $cal1=$cal1 ? "data5" : ''; $cal2=$cal2 ? "data6" : ''; INSERT INTO table1 (col2, col3) VALUES ("'.$cal1.'", "'.$cal2.'");