asked on

SQL Update statement

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.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bapkins

ASKER

thanks for that

is this bit correct ?

where c.acname like firstName+'%' )

Multimatic

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?