MySql Import

[Ruan @ Webluno]

Hi there,

Looking for someone with some MySql experience to help me with thellowing.

I have an old database layout like this:



And I need import it to another databse with a layout like this:




In the old database where the account_status is set as 1 it needs to be changed to 0 in the new database.

Where the account_status is 2 in the old database it needs to be 1 in the new database.

Hoping someone has some experience and can guide me in the right direction.

 

[r4nd0m]

You basically want to do a transform before the insert.

Something basic as what you describe can be accomplished with cases in the SELECT.

INSERT INTO NEW_DB.NEW_TABLE(banned, rsa_id)
SELECT
   CASE
      WHEN account_status = 1 THEN 0
      WHEN account_status = 2 THEN 1
      ELSE 0   -- default
    END AS banned,
   rsa_id  -- copy along id unmodified
FROM OLD_DB.OLD_TABLE

Something like that.

 

[neo_]

A case statement is explicit, which is good. If you wanted to shorten it, you could do this. Might even be faster.

INSERT INTO new_table (rsa_id, banned)
SELECT
  rsa_id,
  IF(account_status = 2, 1, 0) AS banned
FROM old_table;

 

[Ruan @ Webluno]

@r4nd0m & @_neo

Do I add this code to the top of the downloaded .SQL file?

 

[Ruan @ Webluno]

@r4nd0m & @_neo The new table has a unique id that starts as 1 but the old database does not have a unique identifier. Will, it automatically create a unique ID for the old entries?

 

[neo_]

@r4nd0m & @_neo

Do I add this code to the top of the downloaded .SQL file?

It's just SQL, so you can run it in whatever client you are using. Assuming you are transferring from one table in an old database to a new table in a new database (as opposed to both tables being in the same database), you will need to specify the database names as shown above.

Also, what do you mean by downloaded SQL file?

@r4nd0m & @_neo The new table has a unique id that starts as 1 but the old database does not have a unique identifier. Will, it automatically create a unique ID for the old entries?

Yes, if the id column has AUTO_INCREMENT set on the new table, then it will.

 

[maumau]

You guys are soooooo clever

 

[GreGorGy]

If those are the only values for account_status in your old table, then it is really simple:

INSERT INTO newtable (rsa , banned)
SELECT rsa , account_status-1 FROM oldtable

If account_status can also be null:

INSERT INTO newtable (rsa , banned)
SELECT rsa , COALESCE(account_status,1)-1 FROM oldtable

If you want to do it after the fact:

UPDATE newtable SET banned=COALESCE(banned,1)-1

 

[Ruan @ Webluno]

I'm so confused right now.

To make things simple I've imported the old database table into my new database.


The old table I've named Numbers and the new table is called idNumbers

I'm assuming that I use the "SQL" tab to run the code mentioned above in phpMyAdmin?

 

[Ruan @ Webluno]

Nevermind, I've figured it out the code from @r4nd0m worked perfectly.

Thank you everyone for helping me out!!!