title: How to insert a field that contains a modified value of a row
body: <p>I have this table:</p>
  
  <pre><code>CREATE TABLE `new_random` (
    `i_domain` varchar(500) DEFAULT NULL,
    `i_domain_no_http` varchar(500) DEFAULT NULL,
    UNIQUE KEY `i_domain_UNIQUE` (`i_domain`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>I have created the field <code>i_domain_no_http</code> using alter table. It is a new column (no value there it is initial set to NULL) where I want to insert the exact values of <code>i_domain</code> but after modifying them and removing a prefix <code>http://</code>./ </p>
  
  <p>I made the query:</p>
  
  <pre><code>insert into `myscheme`.`new_random` (`new_random`.`i_domain_no_http`)
  select substring_index(`new_random`.`i_domain`,'http://',-1)
  from `myscheme`.`new_random`;
  </code></pre>
  
  <p>Example, the table should look like:</p>
  
  <pre><code>i_domain       | i_domain_no_http
  ------------------------------------
  http://11.com  | 11.com
  https://22.com | 22.com
  </code></pre>
  
  <p>But I am getting this error:</p>
  
  <pre><code>i_domain       | i_domain_no_http
  ------------------------------------
  http://11.com  | 
  https://22.com | 
  NULL           | 11.com
  NULL           | 22.com
  </code></pre>
  
  <p>The 'i_domain' should be a primary key and not NULL but I had to remove the <code>NN</code> and <code>PK</code> to avoid the error:</p>
  
  <pre><code>Error Code: 1364. Field 'i_domain' doesn't have a default value
  </code></pre>
  
  <p>What is the problem? How to solve it?</p>