Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Update statement

Posted on 2014-01-23
3
Medium Priority
?
74 Views
Last Modified: 2015-06-17
Hi All,
I have two sql tables in a database.

Companies - contains acname (account name), outlettype
FirstNames - contains 1 column called 'names'

I have a list of our customers in the companies table.
I have a list of individuals first names in the Firstnames table.

I want to
update companies.outlettype to ELEC2 if the acname begins (starts) with any of the names exists in the firstnames.name column

basically i am trying to seperate individuals from companies to segment the data for marketing by updating the outlettype.

I have been doing the following statement on individual records using the following

UPDATE    ACOCMP1.COMPANIES
SET              OUTLETTYPE = 'ELEC2'
WHERE     (ACNAME LIKE 'john %') AND (OUTLETTYPE IS NULL)

But i would like to do this on a large scale with 5495 first names that i have obtained in the FirstNames table

There are around 80,000 records in my companies table so doing them all manually will take a little time :-)

I would be grateful if you could help me with the query to do this mass update.
0
Comment
Question by:bapkins
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 39804584
UPDATE   c
SET              OUTLETTYPE = 'ELEC2'
FROM  ACOCMP1.COMPANIES  c
WHERE EXISTS  (SELECT 1 FROm Firstnames  where c.acname like firstName+'%' )
0
 

Author Comment

by:bapkins
ID: 39804977
thanks for that

is this bit correct ?

where c.acname like firstName+'%' )
0
 
LVL 1

Expert Comment

by:Multimatic
ID: 39806598
To append the % for the wild card search, use CONCAT:

For example:

SELECT 1 FROm Firstnames  where c.acname like LIKE CONCAT(firstName,'%')

Whoops sorry, that was MYSQL syntax.... think that is fine for MSSQL?
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question