SQL INSERT VARIABLE with WHERE clause

Hello Experts;

I am trying to insert a variable (@RefNum) into a SQL table [SRSinvoiceDetail] where the variable (@InvoiceNo) equals an existing value (PND_INVNO) in the table. The query is giving me an 'incorrect syntax near the keyword WHERE' error, and I can't figure out why.

Declare @InvoiceNo as Int =:InvoiceNo
Declare @RefNum as Int =:RefNum

INSERT INTO SRSinvoiceDetail
(PND_RefNum
)
VALUES
(@RefNum
)
WHERE
PND_INVNO=@InvoiceNo

I would appreciate help in correcting the query or the procedure.

Thank you,
Visionet
visionetvAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
INSERT INTO SRSinvoiceDetail (PND_RefNum)
SELECT RefNum
FROM SRSinvoiceDetail
WHERE PND_INVNO=@InvoiceNo
visionetvAuthor Commented:
Hi Aneesh;

Thank you for the structure but the query is not writing anything to the SQL table. Also probably a typo - SELECT should be PND_RefNum which is the column heading.
Aneesh RetnakaranDatabase AdministratorCommented:
I am not clear, can you clearly put the source and target columns and tables
as far as I assume Source and Target Tables are SRSinvoiceDetail , and target column is PND_RefNum.
source column  ???

Also, I am assuming there could be more columns in SRSinvoiceDetail  ? do you want the rest of the values to be null ?
Why are you trying to populate duplicate values ? also, are you sure you want to do an INSERT ? or Is it an UPDATE ?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

visionetvAuthor Commented:
The source is @RefNum  the variable that needs to be inserted into the target column which is PND_RefNum in the SRSinvoiceDetail DB_table

There is an SQL INSERT query that writes data to SRSinvoiceDetail that precedes the RefNum query. The RefNum value is not available to the first query, hence the second query to add the RefNum variable to the SRSinvoiceDetail DB_table.
PortletPaulEE Topic AdvisorCommented:
An INSERT creates NEW ROWS.

It looks much more like you want to perform an UPDATE

e.g.
UPDATE SRSinvoiceDetail

SET [some_unknown_column] = @RefNum

WHERE PND_INVNO=@InvoiceNo

What column in the table SRSinvoiceDetail  will hold the value of @RefNum?
visionetvAuthor Commented:
PND_RefNum
PortletPaulEE Topic AdvisorCommented:
UPDATE SRSinvoiceDetail
SET [PND_RefNum] = @RefNum
WHERE PND_INVNO=@InvoiceNo

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
visionetvAuthor Commented:
Query is still not updating PND_RefNum
PortletPaulEE Topic AdvisorCommented:
That's sad/frustrating but I cannot know why that is the situation as that would require knowing a great deal more about what you are doing and how you are doing it.

It is possible - for example - that the insert transaction  hasn't been committed and hence the subsequent update cannot locate the records. I stress that's an example I do not know if this applies. Another possibility is that the @InvoiceNo value is being lost and hence now rows match "= NULL".

As far as I know I have provided the correct syntax for the update. There has to be records matching that invoice number fr the update to work though.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.