May 13, 2011 at 10:31 am
You are missing column(s) to create a Primary Key on your Vault Table.
You should have a PK on each table.
Also you should define the PartNo as an Index.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 13, 2011 at 10:32 am
OK. Thanks again!
May 15, 2011 at 5:33 am
Check the datatypes... you seem to be fond of varchar(max). Also equality criteria is missing between twotables
May 16, 2011 at 12:00 pm
Dan.Humphries (5/11/2011)
try this. you can not use a like in a join. but by moving it to the where clause it should work fine.
UPDATE Purchasing
SET Purchasing.IsHardware = 1
FROM Purchasing as t1
INNER JOIN Vault_043011 as t2
ON t2.IsPurchased = 1
where t2.PartDesc LIKE 'NUT%'
Dan,
Actually the LIKE operator in a JOIN is perfectly valid. Most operators that are used in a WHERE clause are also valid in a JOIN. It just depends on what you're doing. Take this code:
CREATE TABLE #T1
( RecID INT IDENTITY(1, 1)
, SomeVal VARCHAR(50)
, UpdVal VARCHAR(10))
INSERT INTO #T1
( SomeVal )
VALUES
('One'),
('Two'),
('Three'),
('Four')
CREATE TABLE #T2
( RecID INT IDENTITY(1, 1)
, SomeVal VARCHAR(50))
INSERT INTO #T2
( SomeVal )
VALUES
('One'),
('Two'),
('Three'),
('Four')
SELECT
T1.SomeVal
FROM #T1 T1
INNER JOIN #T2 T2 ON
T1.SomeVal = T2.SomeVal
AND T1.SomeVal LIKE 'T%'
UPDATE T1
SET T1.UpdVal = 'Joined'
FROM #T1 T1
INNER JOIN #T2 T2 ON
T1.SomeVal = T2.SomeVal
AND T1.SomeVal LIKE 'T%'
SELECT * FROM #T1
Todd Fifield
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy