Solved

SQL Insert Into a specified Value where condition exists

Posted on 2011-09-30
6
404 Views
Last Modified: 2012-05-12
I am trying to insert records for where a condition exists and doesn't copy from the previous table.  Basically I have a custom student table, and I want to add a number to the values field on a specified attribute where they have enrollment in a specified calendar.
From the books and knowledge bases I read, it has to copy a value like 21 from one table to another.

INSERT INTO [sandbox].[dbo].[CustomStudent]
           ([value])
values (21)
where CustomStudent.attributeID = '278' and enrollment.calendarid ='62'
INSERT INTO [hopewell_sandbox].[dbo].[CustomStudent]
           ([value])
values (21)
where CustomStudent.attributeID = '278' and enrollment.calendarid ='62'

Open in new window

0
Comment
Question by:Emi975
6 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 100 total points
ID: 36893087
What other columns exist in the CustomStudent table?  Where do you expect the values for those columns to come from?  If you don't select them from somewhere or specify them, then you are, in effect, inserting NULL into those columns.
0
 

Author Comment

by:Emi975
ID: 36893313
customid,personid,enrollmentid,attributeid,value,date are the fields in this table.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 100 total points
ID: 36893418
Depending on the default settings, e.g. date defaulting to GETDATE(), you may be able to leave some of those out.  (By the way, date is a really bad choice for a column name because it is a Reserved Word.  You should change that to, for instance, DateLastUpdated.)

However, if you don't supply anything in your SELECT statement, then you wil, in effect, be inserting NULL into those columns . . . which may or may not work.
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 25 total points
ID: 36893503
is this coursework/homework?

first some definitions

Insert -  add new rows to a table
Update - amend data stored in columns on existing table rows.

two basic forms for the insert statement

insert into tablename
 (list of column names for which data will be supplied)
 Values (data items for a single row matching the order of the column list)

or

Insert into tablename
(list of column names for which data will be supplied)
Select a mixture of columns from tables used in the select statement and data values
FROM Tablename
Where ....


please be more specific about what you are attempting to do

0
 
LVL 8

Expert Comment

by:Leo Torres
ID: 36893540

Dont have enough info about tables but there has to be some thing that joins the 2 tables

the c. and e. would point to columns that exists in there respective tables they are aliases
Should look like this
INSERT INTO [hopewell_sandbox].[dbo].[CustomStudent]
Select c.'Your value', e.'Your Value',e.'Your Value'
from CustomStudent c
	inner Join enrollment e 
	on e.id = c.id
where CustomStudent.attributeID = '278' and enrollment.calendarid ='62'

Open in new window

0
 
LVL 8

Expert Comment

by:Leo Torres
ID: 36893555
For got to mention you must specify what columns your inserting into if not you must enter a value in every column in the same order they appear in the table
INSERT INTO [hopewell_sandbox].[dbo].[CustomStudent](column1,column2,colmn3)
Select c.'Your value', e.'Your Value',e.'Your Value'
from CustomStudent c
	inner Join enrollment e 
	on e.id = c.id
where CustomStudent.attributeID = '278' and enrollment.calendarid ='62'

Open in new window

0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question