T-Sql Update Query

Posted on 2012-08-28
Last Modified: 2012-09-14

I have to update a Report field in TableA basing on length of a string of a field Description in TableB.
For example,

TableA has the following fields:
Item, ItemName, ItemDescription, Report

TableB has the following fields:
Item, ItemExtendedDescription

I use a LEN function to find how many characters are in the field TableB.ItemExtendedDescription

I need a query that will update TableA.Report if it is in a certain range of characters.

So far I've written a query like in the bellow example, however, it doesn't work and UPDATE all rows in TableA.

Thank you in advance for you help!

Update a
Set a.Report = 'ShortReport.asd'
FROM TableA a 
JOIN TableB b
ON a.Item = b.Item
WHERE LEN(b.[ItemExtendedDescription]) <=185

Open in new window

Update a
Set a.Report = 'ShortReport.asd'
FROM TableA a 
JOIN TableB b
ON a.Item = b.Item
WHERE LEN(b.[ItemExtendedDescription]) <=185

Open in new window

Question by:Zaurb
    LVL 18

    Accepted Solution

    Before doing an update, you should do a select. Once you get the select to return the desired rows, then you change it into an update. This is also valid for deletes.

    At first glance, your query seems to be correct. Are you sure there are any values that have more than 185 chars? Try to do this:
    SELECT LEN(b.ItemExtendedDescription) LenIED
    FROM TableA a
    INNER JOIN TableB b
    ON a.Item = b.Item

    And check that the highest value is above 185.
    LVL 1

    Author Closing Comment

    Many thanks!

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Import csv files to MS SQL 5 38
    report c# 9 60
    ms sql + top 1 for each customer 3 25
    Query question 4 10
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    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.
    Viewers will learn how the fundamental information of how to create a table.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now