Delete

[OuTCasT]

I have a table called students. with a column called creditsavailabl e.
now for example say that the specific student has 500 credits and i would like to delete 250 from it.
what delete statement would i use to delete 250 credits from the 500 credits that the student has >???

[amitpatel66]

I have a table called students. with a column called creditsavailabl e.
now for example say that the specific student has 500 credits and i would like to delete 250 from it.
what delete statement would i use to delete 250 credits from the 500 credits that the student has >???

try this:

[code=sql]

UPDATE students set creditsavailabl e = creditsavailabl e - 250;

[/code]

[OuTCasT]

THANKS....
I never knew a person could do that, i have never used something like that before.....than ks again.

[OuTCasT]

I have another problem thati u can mayb help me out with
//

I use a BULK INSERT to insert a entire .csv file of students, name, surname etc

now this will only be done like every 2 months or so, say the 100 students bought more credits and the admin doesnt want to do the changes 1 at a time, using the bulk insert how can u replace the credit values without duplicating the
students ???

the PK for the table is the StudentID, now in the CSV file i have 2 columns which are StudentID and CreditsAvailabl e, now is it possible just to update the credits for those students ???

PLEASE HELP :(

[ck9663]

it really depends on the design of your system. remember that when you update your table the current value will be overwritten by the new one. if you need to refer to those values it'll be gone unless you have a backup. in some dbdesign, they use table or column naming convention to preserve this value.

if we're just follow what you posted as your requirement, i would recommend you just create a stored proc that will read the text file. bcp-into a some sort of transaction table (ie credityyyymm). then do a UPDATE...FROM

hope this helps

-- CK

[jamesd0142]

You can do something like this if it helps:

[code=sql]
update <TableName>
set <col01= '100' where <col02> in ('a', 'b')
[/code]

so where col02 = "a" or "b" col01 will be set to "100"

any help?

[amitpatel66]

I have another problem thati u can mayb help me out with
//

I use a BULK INSERT to insert a entire .csv file of students, name, surname etc

now this will only be done like every 2 months or so, say the 100 students bought more credits and the admin doesnt want to do the changes 1 at a time, using the bulk insert how can u replace the credit values without duplicating the
students ???

the PK for the table is the StudentID, now in the CSV file i have 2 columns which are StudentID and CreditsAvailabl e, now is it possible just to update the credits for those students ???

PLEASE HELP :(

You can create a procedure which will do the following task to achieve your requirement:

1. Read a record from the flat file
2. Check if the student id in the record is already available in the table.
3. If point 2 satisfies, then update the credit available for that student in the table
4. If point 2 does not satisfy, then do an insert wof the particular record.

In this case, bulk insert/update will not be possible.
If you want to perform BULK INSERT or BULK UPDATE, then you need to do the following also:

1. Read through file and find out the studentids that are there in the table and place them in one array.
2. Find out student ids that are not there in the table.
3. Use theses arrays and perform BULK UPDATE for point 1 and BULK INSERT for point 2.

I hope this helps!!

[OuTCasT]

You can create a procedure which will do the following task to achieve your requirement:

1. Read a record from the flat file
2. Check if the student id in the record is already available in the table.
3. If point 2 satisfies, then update the credit available for that student in the table
4. If point 2 does not satisfy, then do an insert wof the particular record.

Hi, not to clued up with Stored Procedures, but i know what u are aiming at.
thanks....

[OuTCasT]

it really depends on the design of your system. remember that when you update your table the current value will be overwritten by the new one. if you need to refer to those values it'll be gone unless you have a backup. in some dbdesign, they use table or column naming convention to preserve this value.

if we're just follow what you posted as your requirement, i would recommend you just create a stored proc that will read the text file. bcp-into a some sort of transaction table (ie credityyyymm). then do a UPDATE...FROM

hope this helps

-- CK

Yeah i dont need to refer to the previous values.
Say the students pay it into the colleges bank acc, and they make a .csv file with all the StudentID's and Credits in it.
The admin person wld just have to upload the .csv file with the system, first the .csv file will have to be inserted into a table for viewing, otherwise the new total wld just update the old total, instead of adding the new total to the already existing total....if the total was not on 0.

i did a manual version

2 textboxes and a button

UPDATE [credit] SET [credit] = [credit] + ' " & txtCredit.text & " ' WHERE studentid = ' " & txtStudentID & " '

but that wld be manual.....

[ck9663]

Yeah i dont need to refer to the previous values.
Say the students pay it into the colleges bank acc, and they make a .csv file with all the StudentID's and Credits in it.
The admin person wld just have to upload the .csv file with the system, first the .csv file will have to be inserted into a table for viewing, otherwise the new total wld just update the old total, instead of adding the new total to the already existing total....if the total was not on 0.

i did a manual version

2 textboxes and a button

UPDATE [credit] SET [credit] = [credit] + ' " & txtCredit.text & " ' WHERE studentid = ' " & txtStudentID & " '

but that wld be manual.....

MANUAL:
1. view the CSV file using some sort of a text editor/viewer (ie EXCEL)
2. using DTS, upload the CSV into some sort of transaction table using a predefined table naming convention. this can be used as your history and will be easier for your table/file management.
3. create a procedure that you can re-use everytime, something like:

CREATE PROCEDURE UpdateCreditMasterTable (@yyyymm as nvarchar)
AS
  declare @sqlstring as varchar(5000)
  
 if exists(select 1 from sysobjects where name = "TRANS" + @yyyy)
   BEGIN
     set @sqlstring = "UPDATE CreditMasterTable
                            set CREDIT = CreditColumnOnYourTransactionFile
                            from TRANS" + @yyyymm + TransTable
                           " WHERE CreditMasterTable.dbo.StudentID = TransTable.dbo.StudentID "
      exec @sqlstring
   END
GO

couple of considerations:

1. you might also want to consider a transaction based so that you can rollback if there are errors.
2. this is a one-time update, once overwritten, no way to go back to previous state, back-up your table.
3. i have not completely tested that stored proc (lazy me), but i hope you get the idea.

-- CK

[OuTCasT]

MANUAL:
1. view the CSV file using some sort of a text editor/viewer (ie EXCEL)
2. using DTS, upload the CSV into some sort of transaction table using a predefined table naming convention. this can be used as your history and will be easier for your table/file management.
3. create a procedure that you can re-use everytime, something like:

CREATE PROCEDURE UpdateCreditMasterTable (@yyyymm as nvarchar)
AS
  declare @sqlstring as varchar(5000)
  
 if exists(select 1 from sysobjects where name = "TRANS" + @yyyy)
   BEGIN
     set @sqlstring = "UPDATE CreditMasterTable
                            set CREDIT = CreditColumnOnYourTransactionFile
                            from TRANS" + @yyyymm + TransTable
                           " WHERE CreditMasterTable.dbo.StudentID = TransTable.dbo.StudentID "
      exec @sqlstring
   END
GO

couple of considerations:

1. you might also want to consider a transaction based so that you can rollback if there are errors.
2. this is a one-time update, once overwritten, no way to go back to previous state, back-up your table.
3. i have not completely tested that stored proc (lazy me), but i hope you get the idea.

-- CK

Error messages

Msg 137, Level 15, State 2, Procedure UpdateCreditMasterTable, Line 5
Must declare the scalar variable "@yyyy".
Msg 156, Level 15, State 1, Procedure UpdateCreditMasterTable, Line 10
Incorrect syntax near the keyword 'WHERE'.

[ck9663]

try:

      CREATE PROCEDURE UpdateCreditMasterTable (@yyyymm as nvarchar)
      AS
       declare @sqlstring as varchar(5000)
       if exists(select 1 from sysobjects where name = "TRANS" + @yyyymm)
         BEGIN
           set @sqlstring = "UPDATE CreditMasterTable
                                  set CREDIT = CreditColumnOnYourTransactionFile
                                  from TRANS" + @yyyymm + " TransTable WHERE CreditMasterTable.dbo.StudentID = TransTable.dbo.StudentID "
            exec @sqlstring
         END
      GO

[OuTCasT]

try:

      CREATE PROCEDURE UpdateCreditMasterTable (@yyyymm as nvarchar)
      AS
       declare @sqlstring as varchar(5000)
       if exists(select 1 from sysobjects where name = "TRANS" + @yyyymm)
         BEGIN
           set @sqlstring = "UPDATE CreditMasterTable
                                  set CREDIT = CreditColumnOnYourTransactionFile
                                  from TRANS" + @yyyymm + " TransTable WHERE CreditMasterTable.dbo.StudentID = TransTable.dbo.StudentID "
            exec @sqlstring
         END
      GO

that works....shot

what i deducted from my problem is that i need a stored proc that when updating the table with a .csv file with students in the .csv file that already exist in the table.....what the proc must do is check the table for studentID that already exist and then update there credits adding the credit already available to the new credits, otherwise if the studentID does not exist create the student.