Martin Muldoon Martin Muldoon - 3 months ago 7
SQL Question

Syntax for INSERT INTO SELECT statement

I have a table with an Identity column (ArticleID). I would like to select the last row in the table and insert a value into a particular column (ArticleImage). I already have the value, so I needn't query for it. Where in the SQL statement do I place this value?

INSERT INTO EasyDNNNews (ArticleImage)
SELECT TOP 1 FROM EasyDNNNews
ORDER BY ArticleID DESC

Answer

Like commentator's have hinted, you need an UPDATE statement, not an INSERT. UPDATE, updates a record that already exists, where INSERT creates a whole brand spanking new record.

Yours is a little more complicated than just a UPDATE <table> SET <field>='Somevalue'; since you want to update a specific record based on a sort. You were headed in the right direction with your SELECT TOP 1.. query.

UPDATE EasyDNNNews 
SET ArticleImage = 'YOURVALUE' 
WHERE ArticleID = (SELECT TOP 1 ArticleID FROM EasyDNNNews ORDER BY ArticleID Desc)` 

That subquery fetches the max ArticleID. The outer UPDATE statement then updates that Articleid's record, setting ArticleImage to whatever value you want to stick in there.