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?
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
0
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.
0
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 ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
0
PortletPaulfreelancerCommented:
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?
0
visionetvAuthor Commented:
PND_RefNum
0
PortletPaulfreelancerCommented:
UPDATE SRSinvoiceDetail
SET [PND_RefNum] = @RefNum
WHERE PND_INVNO=@InvoiceNo
0

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
0
PortletPaulfreelancerCommented:
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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.