Solved

Merge MySql fields of table with phpmyadmin and sql statement

Posted on 2009-04-09
4
538 Views
Last Modified: 2013-12-12
Hellow all,

Is it possible to merge fields in a table to a new table? Let me describe the situation. I have an external sql dump that has a birthday table with 18000 birthday dates. Like this:

tablename: birthdays
field 1: id (int 10)
field 2: day (double)
field 3: month (double)
field 4: year (varchar 255)

a record would be like this

| id | day | month | year |
| 1  | 9     | 10      | 1981 |

I'd like to merge these fields (and the contents of the fields) with an sql statement (phpmyadmin) to this:

tablename: birthdays
field1: id (int 10)
field2: birthday (date)

a record would be like this

| id | birthday      |
| 1  | 1981-10-09 |

Can this be done? And how?

greetz,
walter
0
Comment
Question by:wal_toor
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:burningmace
ID: 24106378
Create a new column in your table called BirthdayNew.

UPDATE birthdays SET BirthdayNew = year + '-' + month + '-' + day

Then delete the old columns and rename BirthdayNew to whatever you want.
0
 
LVL 8

Author Comment

by:wal_toor
ID: 24106519
Hmm zero rows affected? Should this sql statement handle all the 12000 records?

greetz,
walter
0
 
LVL 5

Accepted Solution

by:
burningmace earned 50 total points
ID: 24106634
Sorry, forgetting MySQL requires concat.

UPDATE birthdays SET birthdate = concat(year, '-', month, '-', day);

Remember that you need birthdate to be a VARCHAR column.
0
 
LVL 8

Author Closing Comment

by:wal_toor
ID: 31568477
Great!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
migrating to phpbb forum from vBulletin 4.2 3 37
deprecated mysql extensions 1 27
Get data from two MySQL tables 6 26
Error 438 6 15
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question