Solved

SQL Update statement

Posted on 2014-01-23
3
62 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 500 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Sever Import/export problem 7 44
How to add a suffix to a value in a column based on the value in another column 4 30
Need sql in string 2 28
IF SQL Query 12 26
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

679 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