How to make function of SQL Queries

Question

I have the the following query and I need to run it many times with different names, But How can I make a function of all the queries so that I only need to run that function instead of all the queries one by one.

update clean.vehicles set make='Ford' where type ilike '%explorer%'
update clean.vehicles set make='Chevrolet' where type ilike '%lumina%'
update clean.vehicles set make='Ford' where type ilike '%crown%'
update clean.vehicles set make='Subaru' where type ilike '%suburu%'
update clean.vehicles set make='Subaru' where type ilike '%legacy%'
update clean.vehicles set make='Infiniti' where type ilike '%infinitie%'
update clean.vehicles set make='Ford' where type ilike '%windstar%'
update clean.vehicles set make='Volkswagen' where type ilike '%vw%'
update clean.vehicles set make='Mitsubishi' where type ilike '%mitsubishi%'
update clean.vehicles set make='Infiniti' where type ilike '%infiniti%'
update clean.vehicles set make='Chevrolet' where type ilike '%chev%'
update clean.vehicles set make='Chrysler' where type ilike '%plymouth%'    
update clean.vehicles set make='Chrysler' where type ilike '%plymoth%

Thanks

thursdaysgeek · Accepted Answer · 2013-06-27 17:54:23Z

1

You need a mapping table to look up the make when you specify the type (or vice versa).

Make       Type
Ford       Explorer 
Chevrolet  Lumina 
Ford       Crown 
Subaru     Legacy

Then, when you call your function GetMake(type), it does a query using the Type in the query, returns the Make, and you can then use the results in your query above.

answered Jun 27, 2013 at 17:54

thursdaysgeek

7,99621 gold badges82 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

George · Accepted Answer · 2013-06-27 18:03:48Z

1

Alternatively you could just do...

UPDATE clean.vehicles
SET make = CASE
                WHEN type ilike '%explorer%'
                    THEN 'Ford'
                WHEN type ilike '%lumina%'
                    'Chevrolet'
                ELSE
                    make
           END

This will do an update on every record though non-matching records should be unchanged. If you are only updating a subset of the records then you could add a WHERE clause to only match those types.

answered Jun 27, 2013 at 18:03

George

1,0542 gold badges11 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to make function of SQL Queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related