Updating Data - SQL Server Forums

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)

Updating Data

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-12-17 : 04:52:45

I have this Access Query which I have redone in SQL Server as a StoredProc.

My problem is that running the StoredProc it says its updated the same number of rows as the Access but in fact it hasnt done what the Access Query does, where have I gone astray?

Access Query
UPDATE WRONG_TSLC_ROWS
LEFT JOIN PARAMED_temp
ON WRONG_TSLC_ROWS.EventID = PARAMED_temp.EventID
SET PARAMED_temp.TH_SOR_LOC_CAT = WRONG_TSLC_ROWS.TH_SOR_LOC_CAT_MATRIX

SQL Server
UPDATE WRONG_TSLC_ROWS
SET WRONG_TSLC_ROWS.TH_SOR_LOC_CAT_MATRIX = PARAMED_TEMP.TH_SOR_LOC_CAT
FROM WRONG_TSLC_ROWS
LEFT JOIN PARAMED_TEMP
ON PARAMED_TEMP.EventID = WRONG_TSLC_ROWS.EventID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 04:56:30

You are not setting the values for same table.

Access: UPDATE WRONG_TSLC_ROWS SET PARAMED_temp.TH_SOR_LOC_CAT 
SQL:    UPDATE WRONG_TSLC_ROWS SET WRONG_TSLC_ROWS.TH_SOR_LOC_CAT_MATRIX

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-12-17 : 05:19:59

In SQL Field TH_SOR_LOC_CAT_MATRIX is this as I am taking it from a table which has the samefield in from 2 different tables.

This is my Make Table in SQL
INSERT WRONG_TSLC_ROWS ([TH_SOR_LOC_CAT], [SORRF], [LOC], [TH_SOR_LOC_CAT_MATRIX], [EventID])
SELECT PARAMED_TEMP.[TH_SOR_LOC_CAT], PARAMED_TEMP.[SORRF], PARAMED_TEMP.[LOC],
REF_TH_SOR_LOC_CAT_MATRIX.[TH_SOR_LOC_CAT], PARAMED_TEMP.[EventID]
FROM PARAMED_TEMP
LEFT JOIN REF_TH_SOR_LOC_CAT_MATRIX
ON (PARAMED_TEMP.SORRF = REF_TH_SOR_LOC_CAT_MATRIX.SORRF)
AND (PARAMED_TEMP.LOC = REF_TH_SOR_LOC_CAT_MATRIX.LOC)
WHERE (((PARAMED_TEMP.TH_SOR_LOC_CAT) = @SORLOCCAT))

Changing the line round in SQL as you had done
PARAMED_TEMP.TH_SOR_LOC_CAT = WRONG_TSLC_ROWS.TH_SOR_LOC_CAT_MATRIX
gives me an error
The multi-part identifier "PARAMED_TEMP.TH_SOR_LOC_CAT" could not be bound.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources