table Self-joins with updates


table = PEOPLE

Name Money Type
----- ----- ----
Steve 400 R
Steve 100 R
Paul 500 R
Paul 100 R
Matt 500 R
Matt 200 R
Matt 0 T
Steve 0 T
Paul 0 T

I'm trying to add-up all of the Money values for each Name and store them into their names, but under Type 'T'.

after the update command it should look like this

Name Money Type
----- ----- ----
Steve 400 R
Steve 100 R
Paul 500 R
Paul 100 R
Matt 500 R
Matt 200 R
Matt 700 T
Steve 500 T
Paul 600 T
Posted On: Friday 26th of October 2012 12:03:04 AM Total Views:  394
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Trying to pull data from 2 tables into a query

I'm trying to create a SQL query to pull together a job info table and a customer info table in an Access DB. We can't have a unique ID on the customer that we can store in the job table because the customer info is dumped from Exchange and the ID (row number) in the cust_List table will change each time we export from Exchange. What I need to do is go through jobs, get the CO_ID, the CONTACT_LN and the CONTACT_FN and then match it against Customer_List>Account, Lastname, Firstname. Any ideas
VIEWS ON THIS POST

375

Posted on:

Friday 26th October 2012
View Replies!

Checking duplicate values two tables

Hi , Is it possible in SQL to Restrict value in one table checking a value on anather tables. Scenerio-1. I have two table let say, Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value. Scenerio -2.[/B] I have table called [B]SIC. This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns. Please Advise. Vijay
VIEWS ON THIS POST

437

Posted on:

Friday 26th October 2012
View Replies!

Converting a SQL table into Text file

How to convert a SQL table into Text file I have a table and I want to extract the values with the field names above to a text file. The query should also allow me to define the starting position of the fields in the text file. email: vijay.prasad@in.ibm.com
VIEWS ON THIS POST

401

Posted on:

Friday 26th October 2012
View Replies!

Copying data from one table to another table

I need to copy data from one table into another table. The field names are NOT the same: I need to INSERT Table 1 fields: cdescurl cname cdescription cimageurl category cprice INTO Table 2: product_url name description image_url category price How do I do this Thank you in advance for any help you can give me!
VIEWS ON THIS POST

345

Posted on:

Friday 26th October 2012
View Replies!

copy table to different server

i need a script that can copy a table from a db on one server to another for me, but i have no idea how.. help much appreciated !
VIEWS ON THIS POST

386

Posted on:

Friday 26th October 2012
View Replies!

aggregates from two unrelated tables

Hi I have similar problem and i explained in detail here http://www.balainfo.com/forums/viewtopic.phpt=39 Pls help me to fix this sql Regds Bala
VIEWS ON THIS POST

455

Posted on:

Friday 26th October 2012
View Replies!

Combining info from two tables?

, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other. So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written Sorry if this is a little confusing but im having a hard time visualising how to do this.
VIEWS ON THIS POST

477

Posted on:

Friday 26th October 2012
View Replies!

Help with MS SQL Server - updating data from 2 tables

Hi all, Please forgive me, but I'm new to MS SQL Server and I have few questions. First of all I'd like to know if there is a way to update one table with the data from another. Let's say I have 2 tables. TableA table contains the voucher numbers; TableB table contains the image numbers. Is there a way to merge Voucher numbers from TableA table to Image numbers in TableB table if we assume that there is a primary key Voucher Number in both tables If yes- what would be the query structure for that Please forgive me if you think that this question is silly, I have to say I'm not just new to SQL I'm VERY new to it Thank you in advance.
VIEWS ON THIS POST

376

Posted on:

Friday 26th October 2012
View Replies!

problem w/ linked table permissions

This question involves SQL server as well as Access, so I hope that someone can follow. I'm trying to give someone read and write access to a table in SQL server through a linked table scenario in Access. I set up a new user account with datareader and datawriter permissions and made sure it went into the particular table as well. I also re-created the DSN on the user's local machine using this new login information. However, each time you open up the table after logging in using this info and try to edit a field, it gives a "Write Conflict" error and gives three choices: Save Changes, Copy to Clipboard, Drop Changes and of course, Save Changes is disabled. We also tried logging in using the db admin account and it won't work. Anyone have similar experiences with this If so, is there a work-around so that you can edit data through Access
VIEWS ON THIS POST

367

Posted on:

Friday 26th October 2012
View Replies!

two select statements on one table

Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this. SELECT column1 FROM table1 WHERE column2 = (select column2 from table1 where column3 = 14) Any ideas greatly appreciated.
VIEWS ON THIS POST

363

Posted on:

Friday 26th October 2012
View Replies!

Open table in Query Analyzer

In query Analyzer you can right click on a table and select open. When you do it opens the table as if you were in Enterprise Manager(you can edit the data). Does anyone know the syntax of how to write that in a query so you do not have to right click and select open
VIEWS ON THIS POST

335

Posted on:

Friday 26th October 2012
View Replies!

Classic ASP AddNew record to MsSQL table

Having trouble with a classic asp page, that gives a HTTP 500 - Internal server error when I try to add a new record to a table. I'm guessing the formatting needs to be changed. It was working fine when I was using in a .mdb database Code: rsTicketReviewUpdate.Open "SNA_Ticket_Reviews", adoCon rsTicketReviewUpdate.AddNew rsTicketReviewUpdate.Fields("MAIN_TICKET_ID") = Request.Form("MainTicketID") rsTicketReviewUpdate.Fields("MTTR_Drivers") = Request.Form("MTTR_Drivers") rsTicketReviewUpdate.Fields("MTTR_Actions") = Request.Form("MTTR_Actions") rsTicketReviewUpdate.Fields("OTR_Drivers") = Request.Form("OTR_Drivers") rsTicketReviewUpdate.Fields("OTR_Actions") = Request.Form("OTR_Actions") rsTicketReviewUpdate.Fields("Misc_Notes") = Request.Form("Misc_Notes") rsTicketReviewUpdate.Fields("AdjRpt") = Request.Form("AdjRpt") rsTicketReviewUpdate.Fields("RptCauseInfo") = Request.Form("RptCauseInfo") rsTicketReviewUpdate.Fields("AdjZ_Location") = Request.Form("AdjZLocation") rsTicketReviewUpdate.Fields("AdjXO_CIRCUIT_ID") = Request.Form("AdjXO_CIRCUIT_ID") rsTicketReviewUpdate.Fields("AdjMTTR") = FormatNumber((Request.Form("AdjMTTR")), 2) rsTicketReviewUpdate.Fields("AdjMTTR_Reason") = Request.Form("AdjMTTR_Reason") rsTicketReviewUpdate.Fields("AdjOTR") = FormatNumber((Request.Form("AdjOTR")), 2) rsTicketReviewUpdate.Fields("AdjOTR_Reason") = Request.Form("AdjOTR_Reason") rsTicketReviewUpdate.Fields("AdjPRIORITY") = Request.Form("AdjPRIORITY") rsTicketReviewUpdate.Fields("AdjPRIORITY_Reason") = Request.Form("AdjPRIORITY_Reason") rsTicketReviewUpdate.Update rsTicketReviewUpdate.Close
VIEWS ON THIS POST

152

Posted on:

Wednesday 7th November 2012
View Replies!

Transfer data between two tables in a database

Hi all, I am using MsSQL 5.0 server database. I need to transfer the data from one table to another within a SQL database based on the particular time interval. Is it possible to transfer data among the tables a database automatically. Please help me.
VIEWS ON THIS POST

218

Posted on:

Wednesday 7th November 2012
View Replies!

A little help with a stored procedure- how to update from one table to another

I want to update an existing table from another table. I would like to take the id# from table1 and insert it into table#2 and also update other columns in table #2 with getdate(). I am doing the following. I tried putting the 'studid' inside the parentheses, but it didn't like this... Any tips would be appreciated. Thank you. Code: ALTER PROCEDURE dbo.updCourseRoster AS INSERT INTO [COURSE ROSTER] (DATEADDED,TIMEADDED) VALUES(GETDATE(),GETDATE()), STUDID = STUDENTSMG.STUDENTID SELECT STUDENTID FROM STUDENTSMG WHERE STUDENTSMG.NOTES LIKE '"XLS SOURCE"'
VIEWS ON THIS POST

235

Posted on:

Wednesday 7th November 2012
View Replies!

Displaying SQL table headers & data

Hi , I created a small website about 2 years ago now, and haven't looked at it since. However, I created it with a couple of SQL tables, and now can't remember all the header names to the tables. I remember seeing a nifty bit of code back in the day which when pointed at a table, automatically output the headers (field names) and all the data into a field. Any ideas
VIEWS ON THIS POST

354

Posted on:

Wednesday 7th November 2012
View Replies!

Stored Procedure - Delete some rows (table)

How to I return the row count as result of deleting some rows from a table This this script correct below\t Here's my stored procedure script.. Code: CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw /*************************************************************** ** CREATED: 12/04/2006 **************************************************************** ** DESCRIPTION: Clean the tblSoldRaw by deleting any data that ** is over 90 days old. We need keep the database ** as small as possible so the performance won't ** suffer on the server's and the client's machine. ** **************************************************************** ** NOTES: Hooked up to BookItOut Data Importer Windows Service **************************************************************** ** MODIFICATIONS: ** ** DATE WHO MODIFICATION DESCRIPTION ** ------------------------------------------------------------- ** ***************************************************************/ /*************************************************************** ** Uses: tblSoldRaw ***************************************************************/ AS DELETE FROM tblSoldRaw WHERE TIMESTAMP > (GETDATE() - 90) RETURN ROWCOUNT ERROR_HANDLER: RETURN -1 SET NOCOUNT OFF
VIEWS ON THIS POST

290

Posted on:

Wednesday 7th November 2012
View Replies!

Insert Records from select query into table

I am using a linked server to extract records from a third party system and need to be able to insert these records into a existing table. I have set up the jobs to delete all records at scheduled times and need to reload the table with current data by utilizing the jobs also. Any assistance would be appreciated. cheers Frank
VIEWS ON THIS POST

195

Posted on:

Wednesday 7th November 2012
View Replies!

How do I design secure databases/tables?

I'm working on a project which will require storage of ssn's, credit card numbers and other various sensitive data. Currently I'm using a web host with SQL 2000, my database is in a shared hosting environment. My question and concern is, before I start storing any type of sensitive data, what do I need to know about storing data like this Should it be stored encrypted Should I use a non-shared SQL Server Also, is there a correct way to design this type of information storage I'd appreciate any info, experiences or any information that you can share.
VIEWS ON THIS POST

142

Posted on:

Wednesday 7th November 2012
View Replies!

Delete all records from all tables

Hi , I was testing on my project. Asp.net and MSSQL. Now i need to clean up all tables, means delete all rows from all tables. and also want my primary keys to start up from one again.
VIEWS ON THIS POST

170

Posted on:

Wednesday 7th November 2012
View Replies!

Stored procedures and temporary tables

Hi I am using microsoft sql server 2000 with an access 2003 data project front end. I would like to create a temporary table in a stored procedure and base a form upon this temporary tabole is this possible and if so how Dont know much about how temporary tables work i.e when they are destroyed.
VIEWS ON THIS POST

211

Posted on:

Wednesday 7th November 2012
View Replies!