saurav99990 Posted May 12, 2015 Share Posted May 12, 2015 I want to replace the content of a column in database. I know I should use this : CREATE OR REPLACE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition but I want to do it without any condition. I want only 1 value in my column that's why I am using replace. Does anyone know to do this? If you are not able to understand my question come on teamviewer....I'll explain you. Quote Link to comment Share on other sites More sharing options...
Psycho Posted May 12, 2015 Share Posted May 12, 2015 Huh? I am not understanding you. What do you mean you only want one value? Are there multiple values per record (e.g. comma separated string) or are you saying you want the exact same value for every record? Quote Link to comment Share on other sites More sharing options...
saurav99990 Posted May 12, 2015 Author Share Posted May 12, 2015 We use (INSERT INTO) to insert a record in the table which creates more than one record when used again. Is there any way to add a record and alternately replacing the prevoius one without adding any new record. I know this would work: UPDATE CustomersSET ContactName='Alfred Schmidt', City='Hamburg'WHERE CustomerName='Alfreds Futterkiste'; But what if there is no condition ie. we don't know the record, we only know the column name. Is there any way to fill only one record and alternately replace the previous record without creating 2nd record? Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted May 13, 2015 Share Posted May 13, 2015 You could test for the existence of a record before inserting a new record. If one exists, use update. Otherwise, use insert. Quote Link to comment Share on other sites More sharing options...
Psycho Posted May 13, 2015 Share Posted May 13, 2015 @cyberRobot, that method is not recommended as it can still lead to duplicates due to race conditions. @saurav99990, There is a simple solution: ON DUPLICATE KEY UPDATE. You would need to set a UNIQUE constraint on the appropriate field - in your example it would be "CustomerName". Then you just create an INSERT query with an optional ON DUPLICATE condition. INSERT INTO Customers (CustomerName, ContactName, City) VALUES ('Alfreds Futterkiste', 'Alfred Schmidt', 'Hamburg') '<< Only provide data on this line ON DUPLICATE KEY UPDATE SET ContactName = VALUES(ContactName), SET City = VALUES(City) If a record with the CustomerName 'Alfreds Futterkiste' does not exist, a new record is created with the values for CustomerName, ContactName & City. If a record with that CustomerName does exist, then the fields for ContactName & City will be updated (if the values are different than what exists for the field currently) 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.