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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/ Share on other sites More sharing options...
Ken2k7 Posted May 4, 2009 Share Posted May 4, 2009 There is UPDATE. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-825765 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... Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-825768 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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826028 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? Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826034 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` Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826211 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...... Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826234 Share on other sites More sharing options...
Ken2k7 Posted May 4, 2009 Share Posted May 4, 2009 Are you serious!? I agree with fenway. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826241 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 Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826286 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? Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826294 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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826297 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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826314 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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826316 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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826390 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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826400 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...? Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-826637 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? Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-827253 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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-827272 Share on other sites More sharing options...
flyup Posted May 6, 2009 Author Share Posted May 6, 2009 what would that look like? Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-827280 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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-827284 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? Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-827418 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? Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-827530 Share on other sites More sharing options...
fenway Posted May 6, 2009 Share Posted May 6, 2009 Well, don't update the PK. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-827860 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. Link to comment https://forums.phpfreaks.com/topic/156794-solved-updating-tables/#findComment-828087 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.