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 PurchasingSET 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 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply