SQL Server Update with Join

Glenn Stearns
Glenn Stearns used Ask the Experts™
on
In this query, I am trying to update field 'somast.fstatus' to 'CLOSED' from the result set returned by the SELECT clause. I'm using the JOIN because the field 'fstatus' does not exist in table 'soitem'; it only exists in table 'somast', and the field 'fprodcl' does not exist in 'somast'; it only exists in 'soitem':

SELECT *
FROM somast a
INNER JOIN soitem b on a.fsono = b.fsono
WHERE (a.fsono >= '100000' and a.fsono <= '199999') or (a.fsono >= '700000' and a.fsono<= '799999');

UPDATE somast  
SET a.fstatus = 'CLOSED'
WHERE a.fstatus <> 'CLOSED' and b.fprodcl ='07'  

I can't get this script to work.

I also tried running this script:

UPDATE somast a
INNER JOIN soitem b on a.fsono = b.fsono
SET a.fstatus = 'CLOSED'
WHERE b.fprodcl ='07'  
AND ((a.fsono >= '100000' and a.fsono <= '199999') or (a.fsono >= '700000' and a.fsono<= '799999')) and a.fstatus <> 'CLOSED'

but I get a syntax error saying it doesn't like the 'a' in the UPDATE line.

How can I modify either of these to get this result:

1. SELECT and JOIN records in 'somast' with records in 'soitem' where:
   1. somast.fsono = soitem.fsono
   2. somast.fsono is between '100000' and '199999' or somast.fsono is between '700000' and '799999'
   3. somast.fstatus <> 'CLOSED'
   4. soitem.fprodcl = '07'

2. Then, with the result set,
   1. UPDATE somast.fstatus to 'CLOSED'

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
UPDATE somast
SET fstatus = 'CLOSED'
FROM somast a INNER JOIN
      soitem b on a.fsono = b.fsono
WHERE b.fprodcl ='07' AND
      ((a.fsono >= '100000' and a.fsono <= '199999') or (a.fsono >= '700000' and a.fsono<= '799999')) AND
      a.fstatus <> 'CLOSED'
try this,




UPDATE somast
SET a.fstatus = 'CLOSED'
FROM somast a
INNER JOIN soitem b on a.fsono = b.fsono
WHERE b.fprodcl ='07'  
AND ((a.fsono >= '100000' and a.fsono <= '199999') or (a.fsono >= '700000' and a.fsono<= '799999')) and a.fstatus <> 'CLOSED'

Open in new window

Commented:
There are a few problems and disadvantages with the proprietary "UPDATE with JOIN" syntax. In SQL Server 2008 you should use MERGE instead. In all versions and in ISO Standard SQL you can use EXISTS instead:

UPDATE somast
SET fstatus = 'CLOSED'
WHERE
AND (fsono BETWEEN '100000' AND '199999' OR fsono BETWEEN '700000' AND '799999')
AND fstatus <> 'CLOSED'
AND EXISTS
(SELECT *
 FROM soitem b
 WHERE b.fsono = somast.fsono
 AND b.fprodcl ='07');
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Glenn StearnsAnalyst

Author

Commented:
dportas: Your query returns an error: Incorrect syntax near the keyword 'AND'.

krunal_shah: Your query returns an error: The multi-part identifier "a.fstatus" could not be bound.

patrick: Your query returns an error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Commented:
Ooops, correction:

UPDATE somast
SET fstatus = 'CLOSED'
WHERE
(fsono BETWEEN '100000' AND '199999' OR fsono BETWEEN '700000' AND '799999')
AND fstatus <> 'CLOSED'
AND EXISTS
(SELECT *
 FROM soitem b
 WHERE b.fsono = somast.fsono
 AND b.fprodcl ='07');
check this,
UPDATE somast
SET fstatus = 'CLOSED'
FROM somast 
INNER JOIN soitem b on somast.fsono = b.fsono
WHERE b.fprodcl ='07'  
AND ((somast.fsono >= '100000' and somast.fsono <= '199999') or (somast.fsono >= '700000' and somast.fsono<= '799999')) and somast.fstatus <> 'CLOSED'

Open in new window

Glenn StearnsAnalyst

Author

Commented:
dportas: Now it returns this error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

krunal_shah: Returns same error as dportas: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Commented:
glennes, the error message doesn't match either of the answers posted by myself and krunal because there is no such subquery in them. I suspect that either your UPDATE is different or maybe you have a trigger that is causing the error. I don't think the UPDATE statements given are to blame.
Top Expert 2010

Commented:
glennes,

For that matter, the error message is not consistent with my suggestion either.

Please post the exact SQL you used for each of our suggestions, and as dportas suggested, check to see
whether you have a trigger on that table.

Heck, might not even be on that table: if there are cascading updates, the offending trigger could be on some
other table :)

Patrick
Glenn StearnsAnalyst

Author

Commented:
I copied and pasted your last scripts directly into SQL Management Studio and ran them as you wrote them - no changes. The errors were shown when executing the script.
I've attached a Word document with the code for the triggers on both tables. Hopefully, that will help!
SOITEM---SOMAST-Triggers-Code.doc

Commented:
Your triggers are useless because they assume only one row will be updated. Never write triggers that way.

Look at Books Online for examples of how to write a trigger or post another question.
Glenn StearnsAnalyst

Author

Commented:
dportas: These triggers were written by the software product company whose application we are using.  I've not created nor modified any of them.  But - your point is well taken and I agree!
Commented:
Then the software vendor has limited you to only single row updates. In my opinion their software is broken but they may tell you that they don't support direct updates to the database so it's not their responsibility to fix it.

If you can't modify the trigger you could disable it temporarily using the ALTER TABLE DISABLE TRIGGER statement. ** Be warned this will disable it for every other user however, with possible unexpected consequences **

Or you could write a loop. SET ROWCOUNT 1 so that the UPDATE only does one row at a time and then execute it as many times as you need. It's frustrating that you have to do that just because your software is lousy but maybe it's the safest method.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial