Update command with join syntax

Question

I am migrating from MS Sqlserver to Postgrasql with Babelfish. I cannot get the update commands in my stored procedures to work. I've used Compass and it passes. I have built the following simple procedure that works just fine on MSSql and not in Babelfish.

CREATE TABLE #table1 (keyID int NULL,
	Retail money NULL,
	Cost money NULL,
	GP numeric(20,8) NULL)

CREATE TABLE #table2 (keyID int NULL,
	Retail money NULL,
	Cost money NULL,
	GP numeric(20,8) NULL)

INSERT INTO #table1 (keyID, Retail, Cost, GP)
  Values
(1,10,5,1)
,(2,20,10,1)
,(3,30,15,1)
,(4,40,20,1)

INSERT INTO #table2 (keyID, Retail, Cost, GP)
  Values
(1,0,0,0)
,(2,0,0,0)
,(3,0,0,0)
,(4,0,0,0)

UPDATE #table2
		SET Retail = a.Retail,
			Cost = a.Cost,
			GP = (a.Retail-a.Cost)/a.Retail * 100
	FROM #table2 b
	INNER JOIN #table1 a on a.keyID = b.keyID
 
I get the message:
Msg 33557097, Level 16, State 1, Line 35
missing FROM-clause entry for table "a"

What is the proper syntax?

Answer

You have hit a known issue that is on the roadmap. In the meantime, please rewrite your UPDATE statement as per below:

```
UPDATE #table2
SET Retail = a.Retail, Cost = a.Cost, GP = (a.Retail-a.Cost)/a.Retail * 100
FROM #table2 b, #table1 a
WHERE a.keyID = b.keyID
```

Important: make sure you are running the latest available version of Aurora PostgreSQL and Babelfish, which is this case is APG 14.5 and BBF 2.2. You can check the version through e.g. sqlcmd with: 
```
SELECT CAST( SERVERPROPERTY('BabelfishVersion') AS VARCHAR )
```

Update command with join syntax

Relevant content