Update using joins

Question

How do I update this statement in SQL server?

SELECT *
From std_company
join std_company_receipt 
  on cmp_guid = lcr_cmp_guid
join std_receipt 
  on lcr_rcp_guid = rcp_guid
where cmp_guid = '704BEF57-2740-4E20-B666-60DD25D73DCA';

So something like:

    update std_Receipt
    set rcp_ForceUnitemize = 1
    join std_company_receipt on cmp_guid = lcr_cmp_guid
    join std_receipt on lcr_rcp_guid = rcp_guid
   where cmp_guid = '704BEF57-2740-4E20-B666-60DD25D73DCA'

possible duplicate of How can I do an UPDATE statement with JOIN in SQL? — TTeeple
– TTeeple, Commented Sep 14, 2015 at 17:09
Your "question" needs a little tweaking. You don't mention what column you want to update, what to update it to, etc. Some effort and a little searching the forum for existing solutions would be more beneficial than a "That did not help". — TTeeple
– TTeeple, Commented Sep 14, 2015 at 17:13
update std_Receipt set rcp_ForceUnitemize = 1 join std_company_receipt on cmp_guid = lcr_cmp_guid join std_receipt on lcr_rcp_guid = rcp_guid where cmp_guid = '704BEF57-2740-4E20-B666-60DD25D73DCA' — TrialByError
– TrialByError, Commented Sep 14, 2015 at 17:15

TTeeple · Accepted Answer · 2015-09-14 17:18:53Z

1

You are missing a FROM statement. You should also alias your tables so there is no ambiguity on what tables the column is related to. The link I submitted as a duplicate shows all this.

I guessed on what columns related to what tables since you left that out.

UPDATE rec
SET rec.rcp_ForceUnitemize = 1
FROM std_Receipt rec
INNER JOIN std_company_receipt comp 
   ON rec.cmp_guid = comp.lcr_cmp_guid
INNER JOIN std_receipt rec2 
   ON rec2.lcr_rcp_guid = comp.rcp_guid
WHERE rec.cmp_guid = '704BEF57-2740-4E20-B666-60DD25D73DCA'

answered Sep 14, 2015 at 17:18

TTeeple

2,9791 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TrialByError Over a year ago

Thanks i ended using something a bit different but yes I was missing a few alias'

Collectives™ on Stack Overflow

Update using joins

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related