Results 1 to 3 of 3
Thread: Update Query and Case
2010-12-10, 16:25 #1
- Join Date
- Jun 2009
- Thanked 0 Times in 0 Posts
I have the following query:
SELECT System.Vendor, System.Name, System.Address_1, System.Address_2, System.City, System.State, System.ZIP, System.Country, System.Classification, System.DateOff
FROM System INNER JOIN Vendors ON System.Vendor = Vendors.Vendor
WHERE (((System.Name)<>[Vendors].[VendorName])) OR (((System.Address_1)<>[Vendors].[Address1])) OR (((System.Address_2)<>[Vendors].[Address2])) OR (((System.City)<>[Vendors].[City])) OR (((System.State)<>[Vendors].[State])) OR (((System.ZIP)<>[Vendors].[Zip])) OR (((System.Country)<>[Vendors].[Country])) OR (((System.Classification)<>[Vendors].[Class])) OR (((System.DateOff)<>[Vendors].[DateOff]));
I'd like to somehow take into account case when comparing the two tables. For example: Joe Smith versus JOE SMITH. At the moment, the record is not displayed. I'd like it to display if it is different. I then have a query that updates the vendor table with the record. Any insight would be much appreciated!
2010-12-10, 17:42 #2
- Join Date
- Jun 2002
- Mt Macedon, Victoria, Australia
- Thanked 45 Times in 44 Posts
So you have two tables with similar information ( System and Vendors), and you want to update the data Vendors to the data in Sytsem if any of the details are different?
Why not just run the update query for all records? if they are the same nothing will be changed, and if they are different the update will take place.
Are you saying that when you joing the tables on VendorName you don't get a match if the Case is different? That is not standard behaviour. (or are you saying the exact opposite?)
You could run an Update query on both tables to convert all the Vendor Names to Proper Case
Update VendorName to StrConv([VendorName],3)
3 says to convert to Proper Case - first letter of each word is upper case, rest is lower.
If you look up StrConv in VBA Help you can see the other alternatives.
(PS: Presumably there is some good reason for having two tables with the same info...standard practice is to avoid this situation.)Regards
2010-12-11, 07:09 #3
- Join Date
- Mar 2010
- Winston-Salem, NC USA
- Thanked 10 Times in 9 Posts
You didn't specify which type of database you were using. Here's how you do a case sensitive match in TSQL;
http://sqltips.wordpress.com/2007/05...l-server-2005/Do your part!!! Report SPAM to http://www.spamcop.net/