MySQL Forums
Forum List  »  Newbie

Re: Dropping Column then recreating
Posted by: Phillip Ward
Date: June 14, 2021 05:08AM

Probably because ...

Quote

3 rows in set (0.00 sec)

You removed and added columns, not rows!

Your table started out looking like this:

+-----------+-------+-------+-------+
| jobnumber | ord_y | ord_m | ord_s | 
+-----------+-------+-------+-------+
|       123 |  2021 |     6 |     1 |
|       456 |  2021 |     6 |     2 |
|       789 |  2021 |     6 |     3 |
+-----------+-------+-------+-------+

Then you removed the jobnumber column:

alter table orderform 
  drop jobnumber;

+-------+-------+-------+
| ord_y | ord_m | ord_s | 
+-------+-------+-------+
|  2021 |     6 |     1 |
|  2021 |     6 |     2 |
|  2021 |     6 |     3 |
+-------+-------+-------+

Then you added a column called jobnumber again.

alter table orderform 
  add jobnumber int(8) not null first ;

+-----------+-------+-------+-------+
| jobnumber | ord_y | ord_m | ord_s | 
+-----------+-------+-------+-------+
|         0 |  2021 |     6 |     1 |
|         0 |  2021 |     6 |     2 |
|         0 |  2021 |     6 |     3 |
+-----------+-------+-------+-------+

None of the other data in any of the other columns was touched.

Now, that last bit should have been to add a derived column to work out the value of jobnumber based on the order's year, month and sequence, something like this:

alter table orderform 
  add jobnumber int(8) not null first
  generated always as ( 10000 * ( 100 * ord_y + ord_m ) + ord_s );

+------------+-------+-------+-------+
| jobnumber  | ord_y | ord_m | ord_s | 
+------------+-------+-------+-------+
| 2021060001 |  2021 |     6 |     1 |
| 2021060002 |  2021 |     6 |     2 |
| 2021060003 |  2021 |     6 |     3 |
+------------+-------+-------+-------+


Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: Dropping Column then recreating
June 14, 2021 05:08AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.