tarsier Posted March 10, 2008 Share Posted March 10, 2008 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 Link to comment https://forums.phpfreaks.com/topic/95522-self-referential-sql/ Share on other sites More sharing options...
fenway Posted March 11, 2008 Share Posted March 11, 2008 You DON'T want to do this. Why corrupt the atomicity of each field? Link to comment https://forums.phpfreaks.com/topic/95522-self-referential-sql/#findComment-489492 Share on other sites More sharing options...
tarsier Posted March 11, 2008 Author Share Posted March 11, 2008 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. Link to comment https://forums.phpfreaks.com/topic/95522-self-referential-sql/#findComment-489561 Share on other sites More sharing options...
fenway Posted March 11, 2008 Share Posted March 11, 2008 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. Link to comment https://forums.phpfreaks.com/topic/95522-self-referential-sql/#findComment-489584 Share on other sites More sharing options...
tarsier Posted March 11, 2008 Author Share Posted March 11, 2008 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] Link to comment https://forums.phpfreaks.com/topic/95522-self-referential-sql/#findComment-490020 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.