Solved

Merge MySql fields of table with phpmyadmin and sql statement

Posted on 2009-04-09
4
535 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 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