flyup Posted May 4, 2009 Share Posted May 4, 2009 I am trying to update a single table. I've tried using INSERT but there a few fields that don't match the new table structure. Is there a query that updates identical fields and will skip over fields that aren't in the new table structure? Thanks a lot. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 4, 2009 Share Posted May 4, 2009 There is UPDATE. Quote Link to comment Share on other sites More sharing options...
Maq Posted May 4, 2009 Share Posted May 4, 2009 That seems like it would be a pretty popular operation, someone should write a tutorial about it you could find on Google... Quote Link to comment Share on other sites More sharing options...
flyup Posted May 4, 2009 Author Share Posted May 4, 2009 I tried using UPDATE but when it gets to a field that isn't present in the old table it quits. How do I make it put a NULL value in there and continue the update? Thanks a Lot. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 4, 2009 Share Posted May 4, 2009 Why would a single field not be present? I'm not following. Can you lay out an example? Quote Link to comment Share on other sites More sharing options...
flyup Posted May 4, 2009 Author Share Posted May 4, 2009 "table1" is the old table. "table2" is the new table and includes some fields that are not in "table1" Thanks again. FIELDS in table1: `id`, `type`, `cid`, `locid`, `stid`, `cnid`, `agent_id`, `mls_id`, `viewad`, `viewbooking`, `bookinglink`, `unit_num`, `street_num`, `address1`, `address2`, `postcode`, `county`, `price`, `showprice`, `freq`, `bond`, `priceview`, `age`, `landtype`, `frontage`, `depth`, `bedrooms`, `totalrooms`, `livingarea`, `bathrooms`, `parking`, `mapref`, `declat`, `declong`, `vtour`, `com_feature`, `adline`, `propdesc`, `smalldesc`, `image1`, `image2`, `image3`, `image4`, `image5`, `image6`, `image7`, `image8`, `image9`, `image10`, `image11`, `image12`, `ctown`, `ctport`, `schooldist`, `preschool`, `primaryschool`, `highschool`, `university`, `hofees`, `custom1`, `custom2`, `custom3`, `custom4`, `custom5`, `pool`, `fplace`, `bbq`, `gazebo`, `lug`, `bir`, `heating`, `airco`, `shops`, `schools`, `elevator`, `pets`, `extra1`, `extra2`, `extra3`, `extra4`, `extra5`, `extra6`, `extra7`, `extra8`, `openhouse`, `ohouse_desc`, `takings`, `returns`, `netprofit`, `bustype`, `bussubtype`, `stock`, `fixtures`, `fittings`, `squarefeet`, `percentoffice`, `percentwarehouse`, `loadingfac`, `fencing`, `rainfall`, `soiltype`, `grazing`, `cropping`, `irrigation`, `waterresources`, `carryingcap`, `storage`, `services`, `listdate`, `lastupdate`, `expdate`, `hits`, `sold`, `published`, `checked_out`, `checked_out_time`, `editor`, `owner`, `premium`, `featured`, `metadesc`, `metakey` FIELDS in table2: `id`, `type`, `cid`, `locid`, `stid`, `cnid`, `locality`, `state`, `country`, `office_id`, `mls_id`, `mls_agent`, `viewad`, `viewbooking`, `bookinglink`, `unit_num`, `street_num`, `address2`, `postcode`, `county`, `price`, `showprice`, `freq`, `bond`, `closeprice`, `priceview`, `age`, `landtype`, `frontage`, `depth`, `bedrooms`, `totalrooms`, `livingarea`, `bathrooms`, `parking`, `stories`, `mapref`, `declat`, `declong`, `vtour`, `adline`, `propdesc`, `smalldesc`, `image1`, `image2`, `image3`, `image4`, `image5`, `image6`, `image7`, `image8`, `image9`, `image10`, `image11`, `image12`, `image1desc`, `image2desc`, `image3desc`, `image4desc`, `image5desc`, `image6desc`, `image7desc`, `image8desc`, `image9desc`, `image10desc`, `image11desc`, `image12desc`, `image13`, `image14`, `image15`, `image16`, `image17`, `image18`, `image19`, `image20`, `image21`, `image22`, `image23`, `image24`, `image13desc`, `image14desc`, `image15desc`, `image16desc`, `image17desc`, `image18desc`, `image19desc`, `image20desc`, `image21desc`, `image22desc`, `image23desc`, `image24desc`, `ctown`, `ctport`, `schooldist`, `preschool`, `primaryschool`, `highschool`, `university`, `hofees`, `custom1`, `custom2`, `custom3`, `custom4`, `custom5`, `custom6`, `custom7`, `custom8`, `pool`, `fplace`, `bbq`, `gazebo`, `lug`, `bir`, `heating`, `airco`, `shops`, `schools`, `elevator`, `pets`, `furnished`, `extra1`, `extra2`, `extra3`, `extra4`, `extra5`, `extra6`, `extra7`, `extra8`, `openhouse`, `ohouse_desc`, `takings`, `returns`, `netprofit`, `bustype`, `bussubtype`, `stock`, `fixtures`, `fittings`, `squarefeet`, `percentoffice`, `percentwarehouse`, `loadingfac`, `fencing`, `rainfall`, `soiltype`, `grazing`, `cropping`, `irrigation`, `waterresources`, `carryingcap`, `storage`, `services`, `listdate`, `lastupdate`, `expdate`, `closedate`, `contractdate`, `hits`, `sold`, `published`, `checked_out`, `checked_out_time`, `editor`, `owner`, `premium`, `featured`, `metadesc`, `metakey`, `currency_position`, `currency`, `currency_format`, `assoc_agent`, `email_status`, `ordering`, `schoolprof`, `hoodprof`, `pdfinfo`, `ohdate`, `ohstarttime`, `ohendtime`, `offpeak` Quote Link to comment Share on other sites More sharing options...
fenway Posted May 4, 2009 Share Posted May 4, 2009 yikes... talk about an table in need of normalization...... Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 4, 2009 Share Posted May 4, 2009 Are you serious!? I agree with fenway. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 4, 2009 Share Posted May 4, 2009 Here's a link - http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Quote Link to comment Share on other sites More sharing options...
flyup Posted May 4, 2009 Author Share Posted May 4, 2009 I want update the table and then concentrate on normalization. One at a time. I'm a newbie. Any advice? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 4, 2009 Share Posted May 4, 2009 Sorry, I'm not going to look over that mess of 2 tables. It'll take me too long to compare one column to another to see if it exists and how the SQL should be written for those cases. Quote Link to comment Share on other sites More sharing options...
flyup Posted May 4, 2009 Author Share Posted May 4, 2009 I understand... So you are saying mySQL doesn't have a command that basically says, UPDATE the table if the fields are the same and if they aren't don't do anything? You would have to write something that included all these fields for both the old and new table? That kinda blows. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 4, 2009 Share Posted May 4, 2009 Well you can over-write the data in your second table. Use INSERT...SELECT. Quote Link to comment Share on other sites More sharing options...
flyup Posted May 5, 2009 Author Share Posted May 5, 2009 I was trying to avoid overwriting because I want to use the new fields for additional data. When you say overwrite you mean the INSERT.. SELECT query would update the identical fields but delete the fields that are different in the 2nd table? Hey, thanks a lot for your time. I really do appreciate it. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 5, 2009 Share Posted May 5, 2009 INSERT...SELECT would INSERT new data, but it wouldn't UPDATE anything in the table you're inserting into. But the tables are such a mess it's hard to really say update. It's not impossible though. Depends on your definition of update. Quote Link to comment Share on other sites More sharing options...
fenway Posted May 5, 2009 Share Posted May 5, 2009 Yes, what is your definition of "update" here...? Quote Link to comment Share on other sites More sharing options...
flyup Posted May 6, 2009 Author Share Posted May 6, 2009 My definition of update: Take values in field 'id' (table1) and write them to 'id' in updated table (table2). If the field is not available then don't do anything. Hope that helps? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 6, 2009 Share Posted May 6, 2009 Doesn't really help. I guess you'll have to perform the SQL the very long and hard way. Quote Link to comment Share on other sites More sharing options...
flyup Posted May 6, 2009 Author Share Posted May 6, 2009 what would that look like? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 6, 2009 Share Posted May 6, 2009 Too messy for me to attempt. There's not much you can do with an UPDATE statement. So I guess you'll have to be running *A LOT* of queries to do all those. Possibly about 100. Quote Link to comment Share on other sites More sharing options...
flyup Posted May 6, 2009 Author Share Posted May 6, 2009 Can you show me what one of those queries would look like? Quote Link to comment Share on other sites More sharing options...
flyup Posted May 6, 2009 Author Share Posted May 6, 2009 Hey, I'm using the INSERT query after dropping the tables that don't apply and I'm getting this error: #1062 - Duplicate entry '1' for key 'PRIMARY' What can I do to make the INSERT command work? Quote Link to comment Share on other sites More sharing options...
fenway Posted May 6, 2009 Share Posted May 6, 2009 Well, don't update the PK. Quote Link to comment Share on other sites More sharing options...
flyup Posted May 6, 2009 Author Share Posted May 6, 2009 hey thanks. it worked. I really appreciate your help. 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.