Prev: Add Primary Key enforced with Existing Unique Index: How To
Next: How write this sql query
From: JohnE on 30 May 2010 19:20
Hello. Excuse if I posted this in the wrong area but wasn't sure if it would
go here or an asp.net group since this is involving a webapp.

I have an insert statement that give me a syntax error near Where type
error. The insert is not a long one, which is shown below.

INSERT INTO tblChangeRequest (IsChildOf)
VALUES (ChangeRequestID)
WHERE ChangeRequestID = @changerequestid

Now a brief background on this.
There is one table (tblChangeRequest) that holds the info to insert. One
field (IsChildOf) is listing if the record is a child of another change
request. So, a record can be a parent and/or a child, or neither (null
field). An example is if id 494 can be a parent to 495, 496, and 497. These
three children have 494 in their respective IsChildOf fields. So if I go to
add another child to 494 from the webapp, I am in the edit mode, I select 498
to add as a child. This would then insert 494 into the IsChildOf field of
498.

Can someone see what is wrong with the Insert as to why the error?

Thanks....John
From: Dan Guzman on 30 May 2010 20:32
> INSERT INTO tblChangeRequest (IsChildOf)
> VALUES (ChangeRequestID)
> WHERE ChangeRequestID = @changerequestid

If I understand your example correctly, ChangeManagementID 498 already
exists and you want to specify that it is a child of 494. In that case,
shouldn't this be an UPDATE?

UPDATE dbo.tblChangeRequest
SET IsChildOf = 494
WHERE ChangeRequestID =498.;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"JohnE" <JohnE(a)discussions.microsoft.com> wrote in message
news:01177525-D993-4A6C-9792-5DF2363331B9(a)microsoft.com...
> Hello. Excuse if I posted this in the wrong area but wasn't sure if it
> would
> go here or an asp.net group since this is involving a webapp.
>
> I have an insert statement that give me a syntax error near Where type
> error. The insert is not a long one, which is shown below.
>
> INSERT INTO tblChangeRequest (IsChildOf)
> VALUES (ChangeRequestID)
> WHERE ChangeRequestID = @changerequestid
>
> Now a brief background on this.
> There is one table (tblChangeRequest) that holds the info to insert. One
> field (IsChildOf) is listing if the record is a child of another change
> request. So, a record can be a parent and/or a child, or neither (null
> field). An example is if id 494 can be a parent to 495, 496, and 497.
> These
> three children have 494 in their respective IsChildOf fields. So if I go
> to
> add another child to 494 from the webapp, I am in the edit mode, I select
> 498
> to add as a child. This would then insert 494 into the IsChildOf field of
> 498.
>
> Can someone see what is wrong with the Insert as to why the error?
>
> Thanks....John

From: JohnE on 30 May 2010 22:38
Update. Of course. A record already exists and I'm only updating one field
of the record. How stupid of me not to see it. My mind kept thinking insert
new when update was needed. Maybe I need some time off.

You are correct, the field (IsChildOf) can only be in an existing record and
populated with only an existing record. Update.

Thanks...John




"Dan Guzman" wrote:

> > INSERT INTO tblChangeRequest (IsChildOf)
> > VALUES (ChangeRequestID)
> > WHERE ChangeRequestID = @changerequestid
>
> If I understand your example correctly, ChangeManagementID 498 already
> exists and you want to specify that it is a child of 494. In that case,
> shouldn't this be an UPDATE?
>
> UPDATE dbo.tblChangeRequest
> SET IsChildOf = 494
> WHERE ChangeRequestID =498.;
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "JohnE" <JohnE(a)discussions.microsoft.com> wrote in message
> news:01177525-D993-4A6C-9792-5DF2363331B9(a)microsoft.com...
> > Hello. Excuse if I posted this in the wrong area but wasn't sure if it
> > would
> > go here or an asp.net group since this is involving a webapp.
> >
> > I have an insert statement that give me a syntax error near Where type
> > error. The insert is not a long one, which is shown below.
> >
> > INSERT INTO tblChangeRequest (IsChildOf)
> > VALUES (ChangeRequestID)
> > WHERE ChangeRequestID = @changerequestid
> >
> > Now a brief background on this.
> > There is one table (tblChangeRequest) that holds the info to insert. One
> > field (IsChildOf) is listing if the record is a child of another change
> > request. So, a record can be a parent and/or a child, or neither (null
> > field). An example is if id 494 can be a parent to 495, 496, and 497.
> > These
> > three children have 494 in their respective IsChildOf fields. So if I go
> > to
> > add another child to 494 from the webapp, I am in the edit mode, I select
> > 498
> > to add as a child. This would then insert 494 into the IsChildOf field of
> > 498.
> >
> > Can someone see what is wrong with the Insert as to why the error?
> >
> > Thanks....John
>
From: --CELKO-- on 31 May 2010 11:36
>> INSERT INTO ChangeRequested (child_flg)
VALUES (change_request_id)
WHERE change_request_id = @change_request_id;

INSERT INTO does not have a WHERE clause. Good SQL programmers do not
violate ISO-11179 and put affixes like "tbl-" on things nor would we
use flags in SQL. We also know that fields are not anything like
columns and records are not rows.

Why not post DDL after you read an intro book on SQL? There are much
better ways to model a hierarchy.

>> Can someone see what is wrong with the Insert as to why the error? <<

This is basic syntax; you however bothered to even Google it.
 | 
Pages: 1
Prev: Add Primary Key enforced with Existing Unique Index: How To
Next: How write this sql query