Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

SQL Insert Into a specified Value where condition exists

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
Emi975
Asked:
Emi975
3 Solutions
 
8080_DiverCommented:
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
 
Emi975Author Commented:
customid,personid,enrollmentid,attributeid,value,date are the fields in this table.
0
 
8080_DiverCommented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LowfatspreadCommented:
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
 
Leo TorresSQL DeveloperCommented:

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
 
Leo TorresSQL DeveloperCommented:
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now