Update query not working - 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 2000 Forums

SQL Server Development (2000)

Update query not working

Author

Topic

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2007-05-17 : 15:50:04

I have a table I wish to update the data to another table. Table to be updated is PickMaster, table whre data is coming from is Pickmaster1. When I run the query in access, it works...

UPDATE dbo_PickMaster1 INNER JOIN dbo_PickMaster ON dbo_PickMaster1.PMPalletID = dbo_PickMaster.PMPalletID SET dbo_PickMaster.PMPalletID = [dbo_PickMaster1].[PMPalletID], dbo_PickMaster.PMODNum = [dbo_PickMaster1].[PMODNum], dbo_PickMaster.PMDate = [dbo_PickMaster1].[PMDate], dbo_PickMaster.PMPickBy = [dbo_PickMaster1].[PMPickBy], dbo_PickMaster.PMPickQty = [dbo_PickMaster1].[PMPickQty]

I wish to run this in sql query analyzer as it runs 100 times faster; however, I receive an error stating :

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.

Any ideas what is wrong? And, does the sql look right?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-17 : 16:29:25

Try this syntax:

update PickMaster set ... from PickMaster, Pickmaster1 where ...

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2007-05-17 : 16:36:09

Thanks for the helpful reply.

I have this code currently...

UPDATE
dbo_PickMaster
SET
(PMPalletID,PMODNum,PMDate,PMPickBy,PMPickQty)=
(Select
PMPalletID,PMODNum,PMDate,PMPickBy, PMPickQty
from
dbo_PickMaster1
where
PickMaster1.PMPalletID=PickMaster.PMPalletID)

and I get an error stating :

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '('.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-18 : 01:32:37

Try some thing like

UPDATE dbo_PickMaster1
SET dbo_PickMaster.PMPalletID = [dbo_PickMaster1].[PMPalletID],
dbo_PickMaster.PMODNum = [dbo_PickMaster1].[PMODNum],
dbo_PickMaster.PMDate = [dbo_PickMaster1].[PMDate],
dbo_PickMaster.PMPickBy = [dbo_PickMaster1].[PMPickBy],
dbo_PickMaster.PMPickQty = [dbo_PickMaster1].[PMPickQty]
FROM dbo_PickMaster1
INNER JOIN dbo_PickMaster ON dbo_PickMaster1.PMPalletID = dbo_PickMaster.PMPalletID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-18 : 04:21:26

[code]
UPDATE u
SET PMPalletID = s.PMPalletID,
PMODNum = s.PMODNum,
PMDate = s.PMDate,
PMPickBy = s.PMPickBy,
PMPickQty = s.PMPickQty
FROM dbo_PickMaster u INNER JOIN dbo_PickMaster1 s
ON u.PMPalletID = s.PMPalletID
[/code]

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2007-05-18 : 08:35:01

Thanks guys! The code that worked was:

UPDATE PickMaster
SET PickMaster.PMPalletID = [PickMaster1].[PMPalletID],
PickMaster.PMODNum = [PickMaster1].[PMODNum],
PickMaster.PMDate = [PickMaster1].[PMDate],
PickMaster.PMPickBy = [PickMaster1].[PMPickBy],
PickMaster.PMPickQty = [PickMaster1].[PMPickQty]
FROM PickMaster1
INNER JOIN PickMaster ON PickMaster1.PMPalletID = PickMaster.PMPalletID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-18 : 08:41:59

what exactly is your table name ?
In your original post, it was dbo_PickMaster and dbo_PickMaster1

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2007-05-18 : 09:42:44

PicmMaster and PickMaster1...i wish to update PickMaster. the dbo_ is something sql usually adds in front whild doing a view, but this time I had to take it out.

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2007-05-18 : 09:49:56

I'm not exactly sure the update did take place completely, though, as some data in PickMaster didn't change. Did I just update PickMaster1 instead of PickMaster?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-18 : 09:57:25

quote:
the dbo_ is something sql usually adds in front whild doing a view

How do you create the view ? Or do you mean dbo.PickMaster ?

quote:
Did I just update PickMaster1 instead of PickMaster?

According to the last query that you posted, you update PickMaster with data from PickMaster1

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2007-05-18 : 12:00:23

Ya, I meant dbo.PickMaster.

After re-thinking this process, I am going to have to dump the update as there can be duplicate pallet id's...so I am going to do another process ot restore the data. Thanks for the help though!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources