CODEWITHSUNDEEP
sqlfirebirdfirebird2.5
Simon King
Simon King

Reputation: 195

Update a Table using a Join

I wish to update a table using, but need to use another table to get the correct field. The new information is not taken from another field from another table.

The following SQL statement returns the correct information:

SELECT PURCHASEHEADER.ORDERNOTES
FROM PURCHASEHEADER, ASSEMBLYLINESOURCE
WHERE ASSEMBLYLINESOURCE.HEADERSYSUNIQUEID = 72637001
  AND PURCHASEHEADER.ORDERNUMBER = ASSEMBLYLINESOURCE.PURCHASEORDERNUMBER

I have tried the following:

UPDATE PURCHASEHEADER SET PURCHASEHEADER.ORDERNOTES = 'Updated'
WHERE EXISTS (
   SELECT 1 FROM ASSEMBLYLINESOURCE 
   WHERE PURCHASEHEADER.ORDERNUMBER = ASSEMBLYLINESOURCE.PURCHASEORDERNUMBER
) AND ASSEMBLYLINESOURCE.HEADERSYSUNIQUEID = 72637001

An error is returned saying: " ...Column Unknown ASSEMBLYLINESOURCE.HEADERSYSUNIQUEID..." but it does exist as it works in the first query.

I have seen similar posts from Mark Rotteveel dated July 2017, but still can't get it to work.

Upvotes: 0

Views: 69

Answers (1)

Sajit Kurup
Sajit Kurup

Reputation: 60

There is an issue with your closing bracket. Try this, it worked for me.

UPDATE PURCHASEHEADER set PURCHASEHEADER.ORDERNOTES = 'Updated'
WHERE EXISTS (SELECT 1 FROM ASSEMBLYLINESOURCE WHERE    
PURCHASEHEADER.ORDERNUMBER = ASSEMBLYLINESOURCE.PURCHASEORDERNUMBER AND  
ASSEMBLYLINESOURCE.HEADERSYSUNIQUEID = 72637001)

Upvotes: 1

Related Questions