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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
The code does run successfully now but I get a result of 0 rows changed.
The code does run successfully now but I get a result of 0 rows changed.
Did you use my code from the first posted reply?
ASKER
I did!
In your opening question you joined the two tables by: table1.description = table2.BD
Is that three correct two fields?
Is that three correct two fields?
"The" correct two fields, rather?
ASKER
Yes, those are correct!
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.
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.
ASKER
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!
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!
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,
Not cool that this would work, as it would mean garbage is coming in from Excel, but it is something to rule out.
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,
Not cool that this would work, as it would mean garbage is coming in from Excel, but it is something to rule out.
ASKER
Sorry, your first select statement returned 0 results.
The trim statements also return 0 results...
The trim statements also return 0 results...
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
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
ASKER
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.
What's the best way to get you the data? I can give you a very little bit of it.
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.
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.
ASKER
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!
Thank you!
ASKER
Thank you!
UPDATE table1
SET table1.enddate = '2015-06-01 00:00:00.000'
WHERE
EXISTS(
SELECT 1
FROM table2
WHERE table2.Description = table1.Description
)