Solved

Merge MySql fields of table with phpmyadmin and sql statement

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Read about achieving the basic levels of HRIS security in the workplace.
The viewer will learn how to count occurrences of each item in an array.
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 …

929 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

11 Experts available now in Live!

Get 1:1 Help Now