{"id":724,"date":"2014-09-09T13:58:58","date_gmt":"2014-09-09T13:58:58","guid":{"rendered":"http:\/\/www.nikola-breznjak.com\/blog\/?p=724"},"modified":"2015-08-17T10:56:05","modified_gmt":"2015-08-17T10:56:05","slug":"how-to-create-a-table-based-on-few-columns-of-another-table-but-also-add-some-additional-columns","status":"publish","type":"post","link":"https:\/\/nikola-breznjak.com\/blog\/stack-overflow\/how-to-create-a-table-based-on-few-columns-of-another-table-but-also-add-some-additional-columns\/","title":{"rendered":"How to create a table based on few columns of another table, but also add some additional columns"},"content":{"rendered":"<p><a href=\"http:\/\/stackoverflow.com\/users\/534755\/nikola\"><img loading=\"lazy\" decoding=\"async\" title=\"profile for Nikola at Stack Overflow, Q&amp;A for professional and enthusiast programmers\" src=\"http:\/\/stackoverflow.com\/users\/flair\/534755.png\" rel=\"lightbox[724]\" alt=\"profile for Nikola at Stack Overflow, Q&amp;A for professional and enthusiast programmers\" width=\"208\" height=\"58\" \/><\/a><br \/>\nI&#8217;m a big fan of <a href=\"http:\/\/stackoverflow.com\/\">Stack Overflow<\/a> and I tend to contribute regularly (am currently in the <a href=\"http:\/\/stackexchange.com\/leagues\/1\/alltime\/stackoverflow\/2008-07-31\/534755?sort=reputationchange#534755\">top 0.X%<\/a>).\u00a0In this category (<a href=\"http:\/\/www.nikola-breznjak.com\/blog\/category\/stack-overflow\/\">stackoverflow<\/a>)\u00a0of posts I will will be posting my top rated questions and answers. This, btw, is allowed as explained in the meta thread <a href=\"http:\/\/meta.stackoverflow.com\/questions\/266971\/can-i-post-so-questions-and-answers-in-a-personal-blog\/266973\">here<\/a>.<\/p>\n<p>So, <a href=\"http:\/\/stackoverflow.com\/questions\/12346864\/create-a-table-based-on-few-columns-of-another-table-but-also-add-some-addition\">my question<\/a>\u00a0was\u00a0as follows:<\/p>\n<p style=\"color: #000000;\">I know I can do something like this:<\/p>\n<pre class=\"lang:default decode:true \">CREATETABLE new_table AS(SELECT field1, field2, field3\r\n    FROM my_table\r\n)<\/pre>\n<p>I&#8217;m wondering how do I add more columns to this create table\u00a0<code>SQL<\/code>, that are not from\u00a0<code>my_table<\/code>, but instead ones that I would write my self and which would be unique to this\u00a0<code>new_table<\/code>\u00a0only.<\/p>\n<p style=\"color: #000000;\">I know I could just make the table with the above\u00a0<code>SQL<\/code>\u00a0and then additionaly (after the command is completed) add the necessary columns, but am wondering if this all could be done in one command, maybe something like this (tried it like that, but didn&#8217;t work):<\/p>\n<pre class=\"lang:default decode:true \">CREATETABLE new_table AS((SELECT field1, field2, field3\r\n    FROM my_table),\r\n    additional_field1 INTEGER NOTNULLDEFAULT1,\r\n    additional_field2 VARCHAR(20)NOTNULLDEFAULT1)<\/pre>\n<p>The answer, by user Omesh,\u00a0was this:<\/p>\n<blockquote>\n<p style=\"color: #000000;\">You can also explicitly specify the data type for a generated column:<\/p>\n<p style=\"color: #000000;\">See\u00a0<a style=\"color: #4a6b82;\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/create-table-select.html\" rel=\"nofollow\">Create Table Select Manual<\/a><\/p>\n<pre class=\"lang:default decode:true \">CREATETABLE new_table\r\n(\r\n additional_field1 INTEGER NOTNULLDEFAULT1,\r\n additional_field2 VARCHAR(20)NOTNULLDEFAULT1)AS(SELECT id, val,1AS additional_field1,1AS additional_field2\r\n FROM my_table\r\n);<\/pre>\n<p>Example:\u00a0<a style=\"color: #4a6b82;\" href=\"http:\/\/sqlfiddle.com\/#!2\/5eea5\/1\" rel=\"nofollow\">SQLFiddle<\/a><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m a big fan of Stack Overflow and I tend to contribute regularly (am currently in the top 0.X%).\u00a0In this category (stackoverflow)\u00a0of posts I will will be posting&hellip;<\/p>\n","protected":false},"author":1,"featured_media":609,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[],"class_list":["post-724","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-stack-overflow"],"_links":{"self":[{"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/posts\/724","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/comments?post=724"}],"version-history":[{"count":5,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/posts\/724\/revisions"}],"predecessor-version":[{"id":1712,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/posts\/724\/revisions\/1712"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/media\/609"}],"wp:attachment":[{"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/media?parent=724"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/categories?post=724"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/tags?post=724"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}