I have a table ("table1") with 3 columns called col1, col2 and col3 (each is VARCHAR) with 4 values as shown below:
col1 col2 col3
datA1 datB1 datC1
datA2
I need an ability to add a data at any time into any column NOT affecting on the other ones. The very popular code in the Internet is that (say, we need to add data only to the columns col2 and col3):
INSERT INTO table1 (col2, col3)
VALUES ('datB2', 'datC2');
But it adds new rows, like below:
col1 col2 col3
datA1 datB1 datC1
datA2
NULL datB2 datC2
What I really need is to fill the row starting with value "datA2" at the column "col1" with new values and get the table like below:
col1 col2 col3
datA1 datB1 datC1
datA2 datB2 datC2
The table has 3 columns and each column responses for a particular type of values (say: name, colour, size). What I need is just a possibility to add new values at any time in a particular column and have them without Null and new rows if it has a free cell before.
I found the solution (a chain of logical operations):
1) CHECK
if there is a cell (in the target column) with values either ""
or NULL
.
2) IF
it has one of those then rewrite the FIRST one keeping the values of the other cells in this row at their places (assumably we use UPDATE
))) ).
3) ELSE
just add a new row with all NULL
s in the other cell in the row.
If we want to add a few values into various columns simultaneously we may prepare our queries for all of those and then execute them simultaneously (sorry for tautology).
If we need to add a few values into the same column within one query we can prepare it, using loops (repeating paragraphs 1 and 2 (or, optionally, 3).