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 Quote Link to comment 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? Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.