0

I am not very experienced with SQL but I have put together a query that I though should update two prices in one table (to add on 20% tax) using the description in another table by joining the tables, here is my problem code:-

UPDATE IT_PRICE
SET ITPrice1 = Price1 + (Price1 * .2), ITPrice2 = Price2 + (Price2 * .2)
INNER JOIN IT_PRICE
ON ITEMS.ID = IT_PRICE.ID
Where ITPrice1 <= 0 AND ITPrice2 <=0 AND ITEMS.Item = 'Jet Tec HP 78'

I have tried this in SQL Express 2008 and full SQL 2012 but I get a syntax error on 'INNER' so I tried to hit it from a different angle by using a WITH statement:-

WITH Item_ID as (
SELECT ITEMS.ID FROM ITEMS WHERE ITEMS.Item = 'Jet Tec HP 78'
)
UPDATE IT_PRICE
SET ITPrice1 = Price1 + (Price1 * .2), ITPrice2 = Price2 + (Price2 * .2)
WHERE ITPrice1 <= 0 AND ITPrice2 <=0 AND ID = Item_ID

And now I get 'Invalid column name Item_ID' when I execute the statement. All I want to do is add tax onto two prices in the IT_PRICE table to all items in ITEMS where I match the description.

If anyone can point me in the right direction it would be very much appreciated.

Thanks.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Roger Whitfield
  • 1
  • 3
  • possible duplicate of [How to do Sql Server CE table update from another table](http://stackoverflow.com/questions/1311236/how-to-do-sql-server-ce-table-update-from-another-table) – Martin Smith Feb 17 '13 at 22:40
  • It seems I have SQL 2008 Compact Edition. I have run it against SQL 2012 full edition and it has completed correctly. Thanks for giving such a quick answer and well formatted code, you have saved me from going to bed with this on my mind. – Roger Whitfield Feb 17 '13 at 22:51

1 Answers1

1

You are missing the FROM clause:

UPDATE IT_PRICE
SET ITPrice1 = Price1 + (Price1 * .2), ITPrice2 = Price2 + (Price2 * .2)
FROM IT_PRICE ITEMS INNER JOIN IT_PRICE
ON ITEMS.ID = IT_PRICE.ID
Where ITPrice1 <= 0 AND ITPrice2 <=0 AND ITEMS.Item = 'Jet Tec HP 78'
StrayCatDBA
  • 2,740
  • 18
  • 25