SQL 2005 Update Query

Posted on 2007-10-21
Last Modified: 2009-11-20
OK you T SQL query experts try this one out;
I  have two tables;
1. "Vendors", containing 200 unique Vendors names [ varchar(30)] and matching Expense Codes [int(2)], 2. "Transactions", containing 20,000 transaction Descriptions [varchar(200)] , ExpenseCodes [int(2)] and $ Values.
A one-to-many relationship exists between Vendors and Transactions and a vendor's name may be located anywhere in the Description field of the "Transactions" table.
I want to update the ExpenseCode field in "Transactions" from the Expense Code data recorded in "Vendors" based on a match of the vendor's name anywhere in the Description feild of the "Transactions" table.
Full Text Indexing is enabled on the Transactions table.

Question by:FillSee
    LVL 142

    Accepted Solution

    this should do:

    UPDATE transaction
       set ExpenseCode = v.ExpenseCode
    FROM transaction t
    JOIN vendors v
      on t.description like '%' + v.names + '%'

    Author Comment

    Fantastic, thanks angelII.  It worked perfectly. Solution accepted first go!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now