CODEWITHSUNDEEP
sqldatabasesql-update
Einar Petersen
Einar Petersen

Reputation: 145

SQL update query

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 will I get the result I expect ? 

UPDATE cars  

SET model='Ford-' WHERE model='Ford'

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: 

  
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', '', '', ''

Comments are very welcome

Upvotes: 0

Views: 511

Answers (3)

HLGEM
HLGEM

Reputation: 96572

if you need an inexact match on 'Ford' but not 'Ford-' because there is something else in the field as well (which Ford- makes me think there might be)

try this:

UPDATE cars   
SET model= Replace(model, 'Ford','Ford-') 
WHERE model like 'Ford%' and model not like 'Ford-%'

Upvotes: 0

Silfverstrom
Silfverstrom

Reputation: 29322

It will look for an exakt match, 

UPDATE cars SET model='Ford-' WHERE model='Ford'

will only alter tables where model is exactly == 'Ford'

Upvotes: 1

Piskvor left the building
Piskvor left the building

Reputation: 92752

You are correct:

... WHERE model='Ford'

is an exact match (only matches "Ford", doesn't match "Ford-"). For a substring match, you'd use

... WHERE model LIKE 'Ford%'

(matches anything that starts with "Ford" - but that's apparently not what you want).

See the documentation for UPDATE and WHERE.

Upvotes: 2

Related Questions