Update Table Using Select Case

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Table Using Select Case

Author

Topic

Hambonehock
Starting Member

5 Posts

Posted - 2007-03-16 : 09:00:10

Good morning,

I am trying to setup a bulk update function using a stored procedure that will take in a PK and use a Select Case to update a second table. The error I am getting on Excecute of Alter Procedure is:

Msg 107, Level 16, State 3, Procedure sp_BulkUpload, Line 22
The column prefix 'dbo.tblBulkUpdate' does not match with a table name or alias name used in the query.

I am fairly certain I am making this more complicated than it needs to be but have not been able to find a solution. Any help is greatly appreciated.

SProc:

(

@strRequestID varchar(100),
@strActionAuthorizer varchar(50),
@dtActionAuthDate datetime,
@strActionAuthLocation varchar(16)

)
AS
SET NOCOUNT OFF;

SELECT DISTINCT
dbo.tblBulkUpdate.strRequestID, dbo.tblBulkUpdate.intFolderID, dbo.tblBulkUpdate.intFileID, CASE dbo.tblBulkUpdate.strPerformAction WHEN Upper('SAVE')
THEN 0 WHEN Upper('DELETE') THEN 1 WHEN Upper('DELETED') THEN 2 END AS strPerformaction
FROM dbo.tblBulkUpdate INNER JOIN
dbo.tblScanResults ON dbo.tblBulkUpdate.strRequestID = dbo.tblScanResults.strRequestID

UPDATE dbo.tblScanResults
SET dbo.tblScanResults.strPerformAction = dbo.tblBulkUpdate.strPerformAction, strActionAuthorizer = @strActionAuthorizer, dtActionAuthDate = @dtActionAuthDate, strActionAuthLocation = @strActionAuthLocation
WHERE (strRequestID = @strRequestID) AND (dbo.tblScanResults.intFolderID = dbo.tblBulkUpdate.intFolderID) AND (dbo.tblScanResults.intFileID = dbo.tblBulkUpdate.intFileID)

Meg. Pffft.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 09:07:08

[code]UPDATE sr
SET sr.strPerformAction = bu.strPerformAction,
sr.strActionAuthorizer = bu.strActionAuthorizer,
sr.dtActionAuthDate = bu.dtActionAuthDate,
sr.strActionAuthLocation = bu.strActionAuthLocation
FROM dbo.tblScanResults AS sr
INNER JOIN dbo.tblBulkUpdate AS bu ON bu.intFolderID = sr.intFolderID AND bu.intFileID = sr.intFileID AND bu.strRequestID = sr.strRequestID[/code]

Peter Larsson
Helsingborg, Sweden

Hambonehock
Starting Member

5 Posts

Posted - 2007-03-16 : 09:18:35

You really are a saint!

One further piece of the puzzle is that I would like transform the text in bu to an int in sr

The following returns error:

Msg 156, Level 15, State 1, Procedure sp_BulkUpload, Line 16
Incorrect syntax near the keyword 'AS'.

UPDATE sr
SET sr.strPerformAction = CASE bu.strPerformAction WHEN Upper('SAVE') THEN 0 WHEN Upper('DELETE') THEN 1 WHEN Upper('DELETED') THEN 2 END AS Action,
sr.strActionAuthorizer = bu.strActionAuthorizer,
sr.dtActionAuthDate = bu.dtActionAuthDate,
sr.strActionAuthLocation = bu.strActionAuthLocation
FROM dbo.tblScanResults AS sr
INNER JOIN dbo.tblBulkUpdate AS bu ON bu.intFolderID = sr.intFolderID AND bu.intFileID = sr.intFileID AND bu.strRequestID = sr.strRequestID

Thank you for the earlier speedy reply. This is the last piece of a puzzle before testing.

Meg. Pffft.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 09:22:07

[code]UPDATE sr
SET sr.strPerformAction = CASE UPPER(bu.strPerformAction)
WHEN 'SAVE' THEN 0
WHEN 'DELETE' THEN 1
WHEN 'DELETED' THEN 2
END,
sr.strActionAuthorizer = bu.strActionAuthorizer,
sr.dtActionAuthDate = bu.dtActionAuthDate,
sr.strActionAuthLocation = bu.strActionAuthLocation
FROM dbo.tblScanResults AS sr
INNER JOIN dbo.tblBulkUpdate AS bu ON bu.intFolderID = sr.intFolderID AND bu.intFileID = sr.intFileID AND bu.strRequestID = sr.strRequestID[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 09:23:23

Note that if bu.strPerformAction has any other value then 'SAVE', 'DELETE', 'DELETED'
the sr.strPerformAction column will be NULL.

Peter Larsson
Helsingborg, Sweden

Hambonehock
Starting Member

5 Posts

Posted - 2007-03-16 : 09:23:30

Sorry. I went blindly unto that good night. Had to plug in my parameters as bu does not have the auth info:

(

@strRequestID varchar(100),
@strActionAuthorizer varchar(50),
@dtActionAuthDate datetime,
@strActionAuthLocation varchar(16)

)
AS
SET NOCOUNT OFF;

UPDATE sr
SET sr.strPerformAction = CASE bu.strPerformAction
WHEN Upper('SAVE') THEN 0
WHEN Upper('DELETE') THEN 1
WHEN Upper('DELETED') THEN 2 END,
sr.strActionAuthorizer = @strActionAuthorizer,
sr.dtActionAuthDate = @dtActionAuthDate,
sr.strActionAuthLocation = @strActionAuthLocation
FROM dbo.tblScanResults AS sr
INNER JOIN dbo.tblBulkUpdate AS bu ON bu.intFolderID = sr.intFolderID AND bu.intFileID = sr.intFileID AND bu.strRequestID = sr.strRequestID

Meg. Pffft.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 09:29:01

[code](
@strRequestID VARCHAR(100),
@strActionAuthorizer VARCHAR(50),
@dtActionAuthDate DATETIME,
@strActionAuthLocation VARCHAR(16)
)
AS

SET NOCOUNT ON

UPDATE sr
SET sr.strPerformAction = CASE UPPER(bu.strPerformAction)
WHEN 'SAVE' THEN 0
WHEN 'DELETE' THEN 1
WHEN 'DELETED' THEN 2
-- ELSE -1 ??? Is this correct? Or should the value be NULL?
END,
sr.strActionAuthorizer = @strActionAuthorizer,
sr.dtActionAuthDate = @dtActionAuthDate,
sr.strActionAuthLocation = @strActionAuthLocation
FROM dbo.tblScanResults AS sr
INNER JOIN dbo.tblBulkUpdate AS bu ON bu.intFolderID = sr.intFolderID AND bu.intFileID = sr.intFileID AND bu.strRequestID = sr.strRequestID
[/code]

Peter Larsson
Helsingborg, Sweden

Hambonehock
Starting Member

5 Posts

Posted - 2007-03-16 : 16:15:00

I guess what I would like to do is skip that record. Is that possible?

Meg. Pffft.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 17:17:57

[code]UPDATE sr
SET sr.strPerformAction = CASE UPPER(bu.strPerformAction)
WHEN 'SAVE' THEN 0
WHEN 'DELETE' THEN 1
WHEN 'DELETED' THEN 2
END,
sr.strActionAuthorizer = @strActionAuthorizer,
sr.dtActionAuthDate = @dtActionAuthDate,
sr.strActionAuthLocation = @strActionAuthLocation
FROM dbo.tblScanResults AS sr
INNER JOIN dbo.tblBulkUpdate AS bu ON bu.intFolderID = sr.intFolderID AND bu.intFileID = sr.intFileID AND bu.strRequestID = sr.strRequestID
WHERE UPPER(bu.strPerformAction) IN ('SAVE', 'DELETE', 'DELETED')[/code]

Peter Larsson
Helsingborg, Sweden

Hambonehock
Starting Member

5 Posts

Posted - 2007-03-19 : 16:05:12

Works like a charm. Thank you very much for your help.

Meg. Pffft.

shijobaby
Starting Member

44 Posts

Posted - 2009-08-20 : 03:05:38

the column prefix does not match with a table name or alias name used in the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources