Correction to an INSERT Statement

Hi I have an insert statement that does not work

The query is
Insert into LOCATION (DEPARTMENT)
select NewDEPARTMENT from HullDEPT where HullDEPT.Location = LOCATION.LOCATION)

How should I correct this?
Gordon
Gordon HughesDirectorAsked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
>>I am trying to get the department values from HullDEPT into the Location.Department field

that means that it cannot be an INSERT statement then because INSERT creates new rows into the table.

>>I am trying to get the department values from HullDEPT into the Location.Department field (ensuring that the HullDEPT.Locations align with the LOCATION>LOCATION fields

You might want to try this:
update L
set Department = HD.NewDEPARTMENT
from Location as L
inner join HullDEPT AS HD
on HD.Location = L.LOCATION

Open in new window

1
 
ste5anSenior DeveloperCommented:
Remove the last parenthesis:

INSERT INTO LOCATION ( DEPARTMENT )
            SELECT NewDEPARTMENT
            FROM   HullDEPT
            WHERE  Location = LOCATION.LOCATION;

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Not entirely sure but I'll throw this out anyways.>
If the above WHERE does not work sounds like you need a JOIN

INSERT INTO LOCATION (DEPARTMENT)
SELECT hd.NewDEPARTMENT 
FROM HullDEPT hd 
   JOIN LOCATION l ON hd.Location = l.LOCATION 

Open in new window

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.

 
Éric MoreauSenior .Net ConsultantCommented:
Your SELECT statement cannot refer to the alias of table in which you are inserting! Your SELECT has to be self sufficient.

I think that the query you mean is:

Insert into LOCATION (DEPARTMENT)
select HD.NewDEPARTMENT 
from HullDEPT AS HD
inner join LOCATION as L
 on L.LOCATION= HD.Location 

Open in new window


But that being said, the query is valid but I don't see how/where it will be used because it is a non-sense to me
0
 
Gordon HughesDirectorAuthor Commented:
OK
The first solution fails aand says
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "LOCATION.LOCATION" could not be bound.

The second solution fails and says
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'LOCATION', table 'MP2Live.dbo.LOCATION'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The third solution also fails and says
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'LOCATION', table 'MP2Live.dbo.LOCATION'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The HullDEPT does not contain duplicates or Nulls

Gordon
0
 
Éric MoreauSenior .Net ConsultantCommented:
before executing the INSERT, just run the SELECT and see what result is returned. You might have surprises.
0
 
Gordon HughesDirectorAuthor Commented:
Ok have run select on both HullDEPT and LOCATION table
Nothing looks amiss
I have checked that all the HullDEPT.Locations exist in the Location table

Any other suggestions
Gordon
0
 
Gordon HughesDirectorAuthor Commented:
I also tried an update statement
Update LOCATION
Set DEPARTMENT = (select NewDEPARTMENT from HullDEPT where HullDEPT.LOCATION = LOCATION.LOCATION)where LOCATION.DEPARTMENT in (select LOCATION from HullDEPT)

But it returned 0 rows affected
0
 
Éric MoreauSenior .Net ConsultantCommented:
have you tried:
select HD.NewDEPARTMENT 
from HullDEPT AS HD
inner join LOCATION as L
 on L.LOCATION= HD.Location

Open in new window


It would be helpful to understand what you are trying to do. You first started with INSERT and now you are showing  an UPDATE!
0
 
Gordon HughesDirectorAuthor Commented:
OK Eric
Have run your last select statement and got back the NewDepartment data
Obviosly it has not updated or inserted data into the location table

I have the table LOCATIO which has Location field in it but without associated Departments (the data show them all as Null) the department field has (FK, varchar (10).null) beside it
I am trying to get the department values from HullDEPT into the Location.Department field (ensuring that the HullDEPT.Locations align with the LOCATION>LOCATION fields

Hope this makes sense
Gordon
0
 
Mark WillsTopic AdvisorCommented:
INSERT adds a new row,
UPDATE changes an existing row,

When getting data from another table, you will need to join

UPDATE LOC set department = newdepartmend
from location LOC
inner join HullDEPT on HullDEPT.Location = LOC.LOCATION

-- or  (not recommended)

UPDATE LOCATION set department = (select newdepartment from HullDEPT where HullDEPT.Location = LOCATION.LOCATION)

-- Or (if needing to insert - which doesnt sound as if you need to)

Insert into LOCATION (DEPARTMENT)
select NewDEPARTMENT
from HullDEPT
inner join LOCATION on HullDEPT.Location = LOCATION.LOCATION
0
 
Mark WillsTopic AdvisorCommented:
Apologies Eric, didnt see your post until after I posted (and refreshed the thread)
0
 
Gordon HughesDirectorAuthor Commented:
Just had to disable and rein able a foreign key then work well
0
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.

All Courses

From novice to tech pro — start learning today.