alright, I'm sure this is a simple solution, but I really don't know much about T-SQL. I have two tables, [temp], and [SectionChanges]. I am using SQL 2005 SP2. Here are the schemas:
Code Snippet
[dbo].[temp](
[Section] [smallint] NOT NULL,
[Mnemonic] [nvarchar](50) NOT NULL,
[Test Name] [nvarchar](200) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Patient Type] [smallint] NOT NULL,
[Nurse Unit] [nvarchar](20) NULL,
[Client#] [int] NOT NULL,
[Item_Qty] [smallint] NOT NULL,
[InputDate] [smalldatetime] NOT NULL,
[Bill_Item_ID] [int] NOT NULL,
[Charge_Item_ID] [int] NOT NULL
)
[dbo].[SectionChanges](
[Mnemonic] [nvarchar](50) NOT NULL,
[Test Name] [nvarchar](200) NOT NULL,
[Section] [smallint] NOT NULL
)
The data I want to use is:
Code Snippet
SELECT Mnemonic, [Test Name], Section, Bill_Item_ID
FROM temp
WHERE (Section = 0) OR
(Section >= 18)
I want to match the two tables on Mnemonic AND [Test Name] and then update the temp table with the value of section from SectionChanges
Thank you all.
Try this:
Code Snippet
UPDATE t
SET Section = sc.Section
FROM temp t
INNER JOIN SectionChanges sc
ON t.Mnemonic = sc.Mnemonic
AND t.[Test Name] = sc.[Test Name]
WHERE (Section = 0) OR
(Section >= 18)
|||Thanks much!|||I'm sorry, it worked once, but now it isn't working any more. I now get the error:
Column or expression 'Section' cannot be updated.
This is the exact query I was using when it worked before
Code Snippet
UPDATE t
SET Section = sc.Section
FROM temp t
INNER JOIN SectionChanges sc
ON t.Mnemonic = sc.Mnemonic
AND t.[Test Name] = sc.[Test Name]
WHERE (t.Section = 0) OR
(t.Section >= 17)
Have you changed anything (ie, table definition, etc.) since the first time you ran it?
No changes to the update code?
|||no
|||alright I got it to work again but I'm baffled. The result changes depending on the query window i enter it. In Management studio, if i right click a table and click open table, then open up the query text and replace it with the update query, it gives that error. If i right click the table and goto script table as update and then paste the query, it works fine. It also doesnt work in a SQL task in SSIS.|||I had to change the where clause to add the alias to the Section column name (I had just copied it from your code before)
but this works:
Code Snippet
create table [dbo].[temp](
[Section] [smallint] NOT NULL,
[Mnemonic] [nvarchar](50) NOT NULL,
[Test Name] [nvarchar](200) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Patient Type] [smallint] NOT NULL,
[Nurse Unit] [nvarchar](20) NULL,
[Client#] [int] NOT NULL,
[Item_Qty] [smallint] NOT NULL,
[InputDate] [smalldatetime] NOT NULL,
[Bill_Item_ID] [int] NOT NULL,
[Charge_Item_ID] [int] NOT NULL
)
create table [dbo].[SectionChanges](
[Mnemonic] [nvarchar](50) NOT NULL,
[Test Name] [nvarchar](200) NOT NULL,
[Section] [smallint] NOT NULL
)
insert into dbo.temp values (0, 'code1', 'test1', getdate(), 0, '1', 1, 1, getdate(), 0, 0)
insert into dbo.temp values (0, 'code2', 'test2', getdate(), 0, '1', 1, 1, getdate(), 0, 0)
insert into dbo.temp values (0, 'code3', 'test3', getdate(), 0, '1', 1, 1, getdate(), 0, 0)
insert into dbo.SectionChanges values('code1', 'test1', 100)
insert into dbo.SectionChanges values('code2', 'test2', 200)
insert into dbo.SectionChanges values('code3', 'test3', 300)
UPDATE t
SET Section = sc.Section
FROM temp t
INNER JOIN SectionChanges sc
ON t.Mnemonic = sc.Mnemonic
AND t.[Test Name] = sc.[Test Name]
WHERE (sc.Section = 0) OR
(sc.Section >= 18)
select *
from temp
|||When I right clicked on [temp] and chose "Open Table" and then replaced the SQL with the update code, it gave me the same error that you received.
I noticed though that SSMS modified the script by inserting "CROSS APPLY t" before the WHERE clause.
Removing this cross apply gets rid of the problem.
(I've never used the SQL window on an OPEN TABLE to do anything like this, I usually use "New Query"...so I have no idea why SSMS decided to change what was pasted in the window.)
As for the SSIS, can you tell me what about it isn't working? Is there an error message? What all is going on in and around the SQL Task causing the problem. Please post anything can about it.
|||Thank you very much for your help, I found out that SSIS was just a dumb mistake on my part. It's really strange that that query window gives different results and different errors. This was 1000 times more efficent than what I was trying to do before in SSIS.
