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?

[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.

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
É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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
É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

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
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
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
Where Statement

From novice to tech pro — start learning today.