creating an update query - 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

General SQL Server Forums

New to SQL Server Programming

creating an update query

Author

Topic

theHydra1975
Starting Member

5 Posts

Posted - 2015-05-05 : 17:47:32

Looking to write an query that will update a field for multiple items, like 1,500.

something like:

UPDATE INMAST
SET FPRICE = 111.11

WHERE
INMAST.FPARTNO = 'xxx'

only issue I'm having is a need to do a JOIN because there's one more condition that must be met from another table, i've tried this:

SET FPRICE = 111.11

JOIN INVCUR
ON
(inmast.fpartno + inmast.frev)= (invcur.fcpartno + invcur.fcpartrev)

WHERE
INMAST.FPARTNO = 'NRE'
AND
invcur.flanycur = 'TRUE'

but that is giving me an error around the JOIN

any ideas?

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 17:58:51

UPDATE INMAST
SET FPRICE = 111.11
FROM INMAST
JOIN INVCUR
ON (inmast.fpartno + inmast.frev) = (invcur.fcpartno + invcur.fcpartrev)
WHERE INMAST.FPARTNO = 'NRE'
AND invcur.flanycur = 'TRUE'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 04:47:36

We always alias the Update Table as "U" so that it is clear which table the UPDATE statement is for e.g.:


UPDATE U
SET ...
FROM MyTable1 AS U
     JOIN MyTable2
          ON ...


UPDATE U
SET ...
FROM MyTable1
     JOIN MyTable2 AS U
          ON ...

we do this even if we only have one table - so that it is consistent in all cases, and if a JOIN is added later then we just need to be sure that the "AS U" alias is on the correct table

theHydra1975
Starting Member

5 Posts

Posted - 2015-05-06 : 09:55:01

tkizer

getting the following error when running your query.

UPDATE is not allowed because the statement updates view "INMAST" which participates in a join and has an INSTEAD OF UPDATE trigger.

appears o not want to update the INMAST table because it is part of a JOIN statement

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 10:18:45

You need to update the underlying table in the INMAST view

EXEC sp_helptext 'INMAST'

should show you the source code for the view.

theHydra1975
Starting Member

5 Posts

Posted - 2015-05-06 : 11:02:38

hi kristen,

that query gave my this:

"-- ******************************************************************************************
"
"
"
" -- **********************************************************************************************************************************************************
"
" -- Create new view and instead of trigger based on the renamed table.
"
"
"
" CREATE VIEW dbo.inmast
"
" AS
"
" SELECT *, dbo.GetItemOnHandQuantity(fac, fpartno, frev) AS fonhand, dbo.GetItemInspectionQuantity(fac, fpartno, frev) AS fqtyinspec,
"
" dbo.GetItemNonNetQuantity(fac, fpartno, frev) AS fnonnetqty, dbo.GetItemInProcessQuantity(fac, fpartno, frev) AS fproqty,
"
" dbo.GetItemOnOrderQuantity(fac, fpartno, frev) AS fonorder, dbo.GetItemCommittedQuantity(fac, fpartno, frev) AS fbook, dbo.GetItemLastIssueDate(fac,
"
" fpartno, frev) AS flastiss, dbo.GetItemLastReceiptDate(fac, fpartno, frev) AS flastrcpt, dbo.GetItemMTDIssues(fac, fpartno, frev, GETDATE()) AS fmtdiss,
"
" dbo.GetItemYTDIssues(fac, fpartno, frev, GETDATE()) AS fytdiss, dbo.GetItemMTDReceipts(fac, fpartno, frev, GETDATE()) AS fmtdrcpt,
"
" dbo.GetItemYTDReceipts(fac, fpartno, frev, GETDATE()) AS fytdrcpt
"
" FROM dbo.inmastx"

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 13:57:16

FPRICE is not in that view. Must be in INVCUR then? Is that a table or a view also?

theHydra1975
Starting Member

5 Posts

Posted - 2015-05-06 : 14:04:50

table

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 14:30:20

quote:
Originally posted by theHydra1975

table

So you can work out what to change now???


UPDATE U
SET FPRICE = 111.11
FROM INMAST
JOIN INVCUR AS U
ON (inmast.fpartno + inmast.frev) = (invcur.fcpartno + invcur.fcpartrev)
WHERE INMAST.FPARTNO = 'NRE'
AND invcur.flanycur = 'TRUE'

YOU NEED TO UNDERSTAND THIS, NOT BLINDLY FOLLOW SUGGESTIONS HERE.

We don't know your database, this update could wreck all your data if it behaves wrongly!!!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-05-06 : 20:02:53

Am I the only one who is puzzled by the join?

Of cource I don't know the structure of your partno's, but suppose you have:

Now you want to update partno 1 rev 2. By the way you join, you might be updating:

partno rev
1      2
2      1
3      0

as the partno+rev=3

Seems to me, a better way is:

JOIN INVCUR
ON inmast.fpartno=invcur.fcpartno
AND inmast.frev=invcur.fcpartrev

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 05:01:12

String concatenation maybe? (or "assumed" string concatenation on Integers?

)

theHydra1975
Starting Member

5 Posts

Posted - 2015-05-07 : 10:24:14

I figured it out.

Thank you all for your time and help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources