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?](http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) — TTeeple, Sep 14 '15 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, Sep 14 '15 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, Sep 14 '15 at 17:15

score 1 · Accepted Answer · answered Sep 14 '15 at 17:18

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'

Thanks i ended using something a bit different but yes I was missing a few alias' — TrialByError, Sep 14 '15 at 17:27

Update using joins

1 Answers1