SQL command

I'm having trouble with what should be a really easy SQL command. I did my training over 8 years ago and use it maybe twice a year so excuse my question if this is really easy.

I have two tables. Table 1 has two relevant columns named description(varchar) and enddate(datetime) with thousands of records. Table 2 has a column named description with 700 records.

I need to update enddate with a certain date on the rows in table 1 that have a match with table 2 using the desciption column.

I thought it would be something like this:

UPDATE table1
SET tabe1.enddate = '2015-06-01 00:00:00.000'
 WHERE Description IN (SELECT table1.Description
                    FROM table1
                              LEFT JOIN table2 on table1.Description = table2.BD
                    WHERE table1.Description = table2.BD)

What am I doing wrong?

Thanks,
Primus
PrimusPilusAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
This should be all you need. The JOIN should restrict the update to only those records in table2 that match. The trick was to alias the UPDATE t1 ... FROM table1 t1:

UPDATE t1 
SET table1.enddate = '2015-06-01'
FROM table1 t1
JOIN table2 t2 ON t2.BD = t1.Description

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
EXISTS is designed just to check for the existence of a row:

UPDATE table1
SET table1.enddate = '2015-06-01 00:00:00.000'
WHERE
    EXISTS(
        SELECT 1
        FROM table2
        WHERE table2.Description = table1.Description
    )
PrimusPilusAuthor Commented:
Thank you!

The code does run successfully now but I get a result of 0 rows changed.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

dsackerContract ERP Admin/ConsultantCommented:
Did you use my code from the first posted reply?
PrimusPilusAuthor Commented:
I did!
dsackerContract ERP Admin/ConsultantCommented:
In your opening question you joined the two tables by:  table1.description = table2.BD

Is that three correct two fields?
dsackerContract ERP Admin/ConsultantCommented:
"The" correct two fields, rather?
PrimusPilusAuthor Commented:
Yes, those are correct!
dsackerContract ERP Admin/ConsultantCommented:
Hi Primus,

Please run the following:

SELECT *
FROM table1 t1
JOIN table2 t2 ON t2.BD = t1.Description

Do you get any data at all?

If not, then there could be something subtle between the value in the BD field and the value in the Description field that makes them not match.

Also, if you get no rows, it may be helpful if you will (1) provide the real table layouts of the two tables and (2) provide a sampling of raw data from both tables, focusing especially one records that you expect should match.

That should yield being able to see why that query is returning zero rows.
PrimusPilusAuthor Commented:
Hi,
Oddly enough I get all results from table 1 on that query. I know there are matches and the two fields are the same, nvarchar255. I did import table 2 from excel using the import tool, does that add spaces or something?

I can't really give samples as it's confidential info. Any other advice?

Thank you very much!
dsackerContract ERP Admin/ConsultantCommented:
The query should have returned results from both tables if you used the "SELECT *" and JOINed them, so not sure if your statement that you got "all results from table 1" meant table 1 only or both.

If you are getting results, then the UPDATE should work the same. It may be possible you have a tab or space character in the field. A few different JOIN criteria to try:

Try this: JOIN table2 t2 ON RTRIM(LTRIM(t2.BD)) = RTRIM(LTRIM(t1.Description))

If that doesn't help, perhaps trying to remove tabs might help.

Try this: JOIN table2 t2 ON REPLACE(t2.BD, CHAR(9), '') = RELACE(t1.Description, CHAR(9), '')

You may need to combine the two:

Try this: JOIN table2 t2 ON RTRIM(LTRIM(REPLACE(t2.BD, CHAR(9), ''))) = RTRIM(LTRIM(RELACE(t1.Description, CHAR(9), '')))

Not cool that this would work, as it would mean garbage is coming in from Excel, but it is something to rule out.
PrimusPilusAuthor Commented:
Sorry, your first select statement returned 0 results.

The trim statements also return 0 results...
dsackerContract ERP Admin/ConsultantCommented:
Without seeing the data, I'm shooting in the dark.  Not only that, but getting zero rows here, and your statement in post #40839275 that "oddly enough I get all results from table 1 on that query " seems contradictory.

Either you were getting results, or you weren't. So let's go back a few steps. Do you get any results with this?

SELECT *
FROM table1 t1
JOIN table2 t2 ON t2.BD = t1.Description

And do you get any results with this:

SELECT *
FROM table1 t1
JOIN table2 t2 ON t2.Description = t1.Description
PrimusPilusAuthor Commented:
Sorry for the confusion...I modified your select statement and put LEFT JOIN out of habit when I was typing it out. Your original statement returns 0 results.

What's the best way to get you the data? I can give you a very little bit of it.
dsackerContract ERP Admin/ConsultantCommented:
Use Excel. Load two set of rows, one for table 1, and another for table 2. Make sure they have headings. I'll be creating temporary tables from them.

Also make sure that there are some match-able and some non-match-able values between the two tables. You can limit the data to just a few fields and rows, so as to not breach any policies, but hopefully to provide enough to analyze the issue.

The only risk with this is that Excel may massage the data and eliminate the anomalies. But we'll see. You're welcome to opt for a CSV file.
PrimusPilusAuthor Commented:
Ok, so you are not going o believe this...the person who game me the data gave me the wrong data. Once I got the right stuff it started to work just fine.

Thank you!
PrimusPilusAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.