User Tools

Site Tools


howto_update_in_an_sql_table

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

howto_update_in_an_sql_table [2012/11/02 07:56] (current)
einarpetersen created
Line 1: Line 1:
 +<fc #​FF0000>​TO be updated!</​fc>​
  
 +====== Howto update in an SQL table ======
 +
 +
 +Say I wanted to perform an update in a database that held information on for example cars in a table by that same name.
 +
 +The update I am looking to perform consists in changing the designation in the table in model from "​Ford"​ to "​Ford-"​ i.e. only changing where the designatin is "​Ford"​ and not in those that already have a model designation of "​Ford-"​
 +
 +By running the below SQL query you will get the result you expect.
 +
 +<​code>​
 +UPDATE cars  ​
 +
 +SET model='​Ford-'​ WHERE model='​Ford' ​   ​
 +</​code>​
 +This in my opinion should update only instances of cars where the model designation is "​Ford"​ and change those to "​Ford-"​ and would not proceed to try to update anything where the designation is "​Ford-"​ already as well, or what ?
 +
 +If it does I was thinking if there is a pure SQL query structure that could be used to ensure that only cars with model = "​Ford"​ was changed and not cars with model = "​Ford-"​
 +
 +I am asking as I am uncertain on the way SQL would identify the search string "​Ford"​ in the query above i.e. will SQL look for an exact match, or will the existence of the word "​Ford-"​ in model trigger a "false positive"​ ? Have not done SQL queries for years.
 +
 +Don't talk about normal forms or the like, I am not responsible for creating the DB/tables etc. I am just looking into possibly cleaning up some of the information in the DB.
 +
 +The table cars looks like this:
 +
 +<​code>​
 +  ​
 +cars  ​
 +'​id',​ '​int(10) unsigned',​ '',​ '​PRI',​ '',​ '​auto_increment'  ​
 +'​something0',​ '​varchar(50)',​ '',​ '​UNI',​ '',​ ''  ​
 +'​something1',​ '​varchar(50)',​ '​YES',​ '',​ '',​ ''  ​
 +'​something2',​ '​varchar(50)',​ '​YES',​ '',​ '',​ ''  ​
 +'​something3',​ '​text',​ '​YES',​ '',​ '',​ ''  ​
 +'​something4',​ '​text',​ '​YES',​ '',​ '',​ ''  ​
 +'​something5',​ '​varchar(50)',​ '​YES',​ '',​ '',​ ''  ​
 +'​something6',​ '​varchar(50)',​ '​YES',​ '',​ '',​ ''  ​
 +'​something7',​ '​varchar(15)',​ '​YES',​ '',​ '',​ ''  ​
 +'​model',​ '​varchar(255)',​ '​YES',​ '',​ '',​ ''  ​
 +</​code>​
howto_update_in_an_sql_table.txt ยท Last modified: 2012/11/02 07:56 by einarpetersen