Solved

Merge MySql fields of table with phpmyadmin and sql statement

Posted on 2009-04-09
4
506 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Great!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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 …

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now