T-Sql Update Query

Posted on 2012-08-28
Medium Priority
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

Cluskitt earned 2000 total points
ID: 38340917
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
ON a.Item = b.Item

And check that the highest value is above 185.

Author Closing Comment

ID: 38398839
Many thanks!

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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