Self Referential SQL

[tarsier]

Hello all,

I am having trouble grasping how to do self referential SQL statements. Let me give you an example. Here is a sample table.

 

[pre]+------------------------------------+

|            table1                |

+-------+-------+-----------+--------+

| id    | name  | phone    | ext    |

+-------+-------+-----------+--------+

| 1    | Ted  | 1111      | NULL  |

| 2    | Dave  | 2222      | 44    |

| 3    | Lucy  | 3333      |        |

| 4    | Ann  | 4444      | 23    |

+-------+-------+-----------+--------+[/pre]

 

I want to take all records that have data in the 'ext' field and append that data into the 'phone' field, whit the prefix of "EXT:" So the result will be the following

 

[pre]+--------------------------------------+

|            table1                  |

+-------+-------+-------------+--------+

| id    | name  | phone      | ext    |

+-------+-------+-------------+--------+

| 1    | Ted  | 1111        | NULL  |

| 2    | Dave  | 2222 EXT:44 | 44    |

| 3    | Lucy  | 3333        |        |

| 4    | Ann  | 4444 EXT:23 | 23    |

+-------+-------+-------------+--------+[/pre]

 

Here is the SELECT statement I've come up with.

 

[pre]SELECT * from FROM table1 WHERE ext IS NOT NULL AND ext != ''[/pre]

 

I can't figure out how to do the actual UPDATE statement. Any help? Any good SQL tutorials people can recommend? Thanks.

 

- Dave

 

 

[fenway]

You DON'T want to do this.  Why corrupt the atomicity of each field?

[tarsier]

Thanks for the response.

 

The reason I need to do this is that I'm moving some customer info from one database to another. The new database does not have an EXT field so I need to merge the two fields into one before I do the move.

 

I could move the data into a new table before I export.

 

[fenway]

Well, yes, make a new table for the export... but I'd highly recommend a new field in the new database.  It's going to be a disaster otherwise.

[tarsier]

Thanks for the advice. I ended up with the following.

 

[pre]UPDATE table1

SET phone_full =

  IF(phone IS NOT NULL AND phone != '',

    IF(EXT IS NOT NULL AND EXT != '', CONCAT(Phone, ' ext.', EXT), phone),

    IF(EXT IS NOT NULL AND EXT != '', EXT, NULL))

WHERE 1[/pre]