Need help with sql command for update records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with sql command for update records

Author

Topic

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 12:27:13

This is the skeleton / structure of my tables:
-----------
Member_Table
-----------

CREATE TABLE [dbo].[MemberTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[Address] [nvarchar](max) NULL, )

.....................................
-----------
Year Table
-----------

CREATE TABLE [dbo].[YearTable](
[YearID] [int] IDENTITY(1,1) NOT NULL,
[PaymentYear] [nvarchar](50) NULL,)

...........................................
-----------
Payment_Table
-----------

CREATE TABLE [dbo].[Payment_Table](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NOT NULL,
[YearID] [int] NOT NULL,
[AmountPaid] [money] NULL,
[PaymentDate] [datetime] NULL,
[Status] [bit] NULL,)
..................................

ID from MembetTable is FK in Payment_Table
YearID from Year Table is FK in Payment_Table
...........................................

In my Payment Table, I have some data updated for Member who made payment in which year.

Now I cannot generate report from my database for member who didn't pay because there is NO record available in Payment Table for those members.

What SQL command I can use in order to quick update and add records in the Payment Table with Members ID who didn't pay for specific year ?

I want command something like, INSERT RECORDS IN PAYMENT TABLE FOR MEMBER WHO DIDNT MAKE PAYMENT FOR SPECIFIC YEAR OR IN OTHER WORDS MEMBERS WHO'S RECORDS ARE NOT AVAILABLE IN PAYMENT TABLE FOR SPECIFIC YEAR

Please help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 12:42:37

You can insert records for Customers who did not pay like shown below.

INSERT INTO Payment_Table
(Id, YearId)
SELECT
	m.Id,
	y.YearId
FROM
	Members_Table m
	CROSS JOIN YearTable y
WHERE
	NOT EXISTS
	(
		SELECT * FROM
		Payment_Table p
		WHERE p.Id = m.Id AND p.YearId = y.yearId
	);

You may want to provide default values for payment date etc.

However, you don't need to do this if your only purpose is to get all members when you do a select. You can do a left join like shown below (and also limit the number of years if you need to via a where clause). If you have a lot of member turn over, this may be a better option - otherwise you would be inserting a lot of empty records into the payment table.

SELECT
	m.Id,m.Name,
	y.PaymentYear,
	p.*
FROM
	Member_Table m
	CROSS JOIN YearTable y
	LEFT JOIN Payment_Table p ON 
		p.ID = m.ID AND p.YearId = y.YearId

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 12:56:01

quote:
Originally posted by sunitabeck

You can insert records for Customers who did not pay like shown below.
SELECT
m.Id,m.Name,
y.PaymentYear,
p.*
FROM
Member_Table m
CROSS JOIN YearTable y
LEFT JOIN Payment_Table p ON
p.ID = m.ID AND p.YearId = y.YearId

1) By this command I am getting all the member (including those who paid for the specific year(s)) - How to get members who DIDN'T pay for the specific year ?

2) Also, in the insert statement that you posted above, how to insert null values for all records and status value as "False" since I need to update / insert the records in the table with members who didn't pay.

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 13:15:16

I have also tried this:


SELECT
	m.ID,m.FirstName,
	y.Year_Of_Payment, y.YearID
FROM
	MemberTable m
	CROSS JOIN YearTable y

WHERE
	NOT EXISTS
	(
		SELECT * FROM
		Payment_Table p
		WHERE p.ID = m.ID AND p.YearID = y.yearID
	);

This gives all the member who unpaid for ALL the years (multiple rows for each member for each year when he/she didn't pay).

How to get member who unpaid for a specific year only ?

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 13:26:06

FYI: This is the same tables you help me in creating a long time ago, [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=160720[/url]

(Hope this provides enough information)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 13:27:17

quote:
Originally posted by asp__developer
1) By this command I am getting all the member (including those who paid for the specific year(s)) - How to get members who DIDN'T pay for the specific year ?

There must be something that I am not seeing - see the test below. I have 3 tables. There are 3 members and 3 years, so there should be 9 rows if everyone paid every year. But, in my payment table there are only two payments. So the query should return the remaining 7, which it does.

CREATE TABLE #Members(id INT);
CREATE TABLE #Years(YearId INT);
CREATE TABLE #Payments (id INT, YearId INT);

INSERT INTO #Members VALUES (1),(2),(3);
INSERT INTO #Years VALUES (2010),(2011),(2012);
INSERT INTO #Payments VALUES (1,2010),(3,2011);

SELECT
	m.Id,
	y.YearId
FROM
	#Members m
	CROSS JOIN #Years y
WHERE
	NOT EXISTS
	(
		SELECT * FROM
		#Payments p
		WHERE p.Id = m.Id AND p.YearId = y.yearId
	);
	
DROP TABLE #Members;
DROP TABLE #Payments;
DROP TABLE #Years;

quote:
2) Also, in the insert statement that you posted above, how to insert null values for all records and status value as "False" since I need to update / insert the records in the table with members who didn't pay.

You would add them as shown below

INSERT INTO Payment_Table
(Id, YearId,Status, PaymentDate)
SELECT
	m.Id,
	y.YearId,
        0 as Status,
        null as PaymentDate 
FROM
	Members_Table m
	CROSS JOIN YearTable y
WHERE

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 13:36:26

This is my statement -

1) How to get members who didn't pay for year ID '50' ?
2) How to get members who didn;t pay for Year_Of_Payment = '2012' ?

SELECT
	m.ID,m.FirstName,
	y.Year_Of_Payment, y.YearID, 0 as Status,
        null as PaymentDate, null as AmountPaid, null as SuggestedDonation
FROM
	MemberTable m
	CROSS JOIN YearTable y

WHERE
	NOT EXISTS
	(
		SELECT * FROM
		Payment_Table p
		WHERE p.ID = m.ID AND p.YearID = Y.YEARid
	);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 13:47:40

You can add to the outer WHERE clause:

--- 1.

WHERE
	NOT EXISTS
	(
		SELECT * FROM
		Payment_Table p
		WHERE p.ID = m.ID AND p.YearID = Y.YEARid
	)
	AND y.YearId = '50';


--- 2.
WHERE
	NOT EXISTS
	(
		SELECT * FROM
		Payment_Table p
		WHERE p.ID = m.ID AND p.YearID = Y.YEARid
	)
	AND y.Year_Of_Payment  = '2012';

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 13:52:53

thanks sunita - you are the BEST !!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 13:55:20

quote:
Originally posted by asp__developer

thanks sunita - you are the BEST !!!

Heh! You are welcome and thanks! I am going to cut this and paste on top of my monitor so people who walk by will see it ;)

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 13:59:56

haha, that's a great idea :)

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 15:16:37

how to insert values for members who didn't pay for year 2012 with status as 0 and rest of the values as null ?

Is this correct ?


INSERT INTO Payment_Table
(ID (WHAT WILL BE THIS Because will come from other table based on who didn't pay ?), YearID(50),AmountPaid(NULL), Status(0), PaymentDate(NULL))
SELECT
	m.ID,m.FirstName, m.LastName,
	y.Year_Of_Payment, y.YearID, 0 as Status,
        null as PaymentDate, null as AmountPaid, null as SuggestedDonation
FROM
	MemberTable m
	CROSS JOIN YearTable y

WHERE
	NOT EXISTS
	(
		SELECT * FROM
		Payment_Table p
		WHERE p.ID = m.ID AND p.YearID = Y.YEARid
	)
AND y.Year_Of_Payment ='2012';

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 15:32:55

The columns in the insert list must exactly match the columns in the select list, one for one, by position. So your statement would need to be like shown below. Since the WHERE clause limits the selected rows to members who have not paid during 2012, all the rows inserted will be for those memebers, and the Status will be inserted as 0. The other rows (which correspond to actual payments) will be unaffected, and if the status of those rows were null, they will remain as null after this insert.

INSERT INTO Payment_Table
  ( -- THIS IS THE INSERT LIST
    ID,         -- 1
    YearID,     -- 2
    AmountPaid, -- 3
    Status,     -- 4
    PaymentDate -- 5
  )
SELECT --- THIS IS THE SELECT LIST
       m.ID,                   -- 1
       -- m.FirstName,
       -- m.LastName,
       -- y.Year_Of_Payment,
       y.YearID,               -- 2
       NULL AS AmountPaid,     -- 3
       0 AS Status,            -- 4
       NULL AS PaymentDate --, -- 5
       -- NULL AS SuggestedDonation
FROM   MemberTable m
       CROSS JOIN YearTable y
WHERE  NOT EXISTS
       (
           SELECT *
           FROM   Payment_Table p
           WHERE  p.ID = m.ID
                  AND p.YearID = Y.YEARid
       )
       AND y.Year_Of_Payment = '2012';

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 15:39:32

so where I am inserting status as 0 and other values as null?

I see status as 0 in select statement, not insert. Will running your above statement will insert the record in the table for all the members who didn't paid for year 2012 and for all these newly added records the status will be set as "0" and rest values as NULL ?

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 15:48:01

actually now i got it, by running th above statement you posted will do everything

I have converted the SQL Statement as Stored Procedure in which I will be passing the year, please see if BOTH correct or no ?

*************TO PASS YEAR ID**************


ALTER PROCEDURE [dbo].[UpdateNotPaidForSpecificYear]
        (
        
        @Year int
        )
AS

INSERT INTO Payment_Table
  ( -- THIS IS THE INSERT LIST
    ID,         -- 1
    YearID,     -- 2
    AmountPaid, -- 3
    Status,     -- 4
    PaymentDate -- 5
  )

SELECT
m.ID,
y.YearID, 
NULL AS AmountPaid,
0 as Status,
NULL AS PaymentDate,
        
FROM
	MemberTable m
	CROSS JOIN YearTable y

WHERE
	NOT EXISTS
	(
		SELECT * FROM
		Payment_Table p
		WHERE p.ID = m.ID AND p.YearID = Y.YEARid
	)
AND y.YearID = @Year;

*************TO PASS YEAR VALUE like "2012" **************


ALTER PROCEDURE [dbo].[UpdateNotPaidForSpecificYear]
        (
        
        @Year int
        )
AS

INSERT INTO Payment_Table
  ( -- THIS IS THE INSERT LIST
    ID,         -- 1
    YearID,     -- 2
    AmountPaid, -- 3
    Status,     -- 4
    PaymentDate -- 5
  )

SELECT
m.ID,
y.YearID, 
NULL AS AmountPaid,
0 as Status,
NULL AS PaymentDate,
        
FROM
	MemberTable m
	CROSS JOIN YearTable y

WHERE
	NOT EXISTS
	(
		SELECT * FROM
		Payment_Table p
		WHERE p.ID = m.ID AND p.YearID = Y.YEARid
	)
AND y.Year_Of_Payment = @Year;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 15:52:28

The short answer is YES to your questions. Yes, it will insert 0 for those members who did not pay. And, it will leave not change the Status column of members who have paid (and hence have entries in the Payment table). So if they were null, they would remain as null.

That whole query I posted in my previous post is just one statement. You can think of it like this. The code in red is saying "I want to insert these columns". We have not yet specified what values to insert. The second part in green answers that question. "Insert the values coming out of this query into those columns". You can run the select part by itself to see what will get inserted. When you run the whole query, the select part gets executed and the output, instead of showing up in the query results window, gets inserted into the table and columns specified in the first part shown in red.

INSERT INTO Payment_Table
  ( -- THIS IS THE INSERT LIST
    ID,         -- 1
    YearID,     -- 2
    AmountPaid, -- 3
    Status,     -- 4
    PaymentDate -- 5
  )
SELECT --- THIS IS THE SELECT LIST
       m.ID,                   -- 1
       -- m.FirstName,
       -- m.LastName,
       -- y.Year_Of_Payment,
       y.YearID,               -- 2
       NULL AS AmountPaid,     -- 3
       0 AS Status,            -- 4
       NULL AS PaymentDate --, -- 5
       -- NULL AS SuggestedDonation
FROM   MemberTable m
       CROSS JOIN YearTable y
WHERE  NOT EXISTS
       (
           SELECT *
           FROM   Payment_Table p
           WHERE  p.ID = m.ID
                  AND p.YearID = Y.YEARid
       )
       AND y.Year_Of_Payment = '2012';

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-10 : 15:57:31

QUICK QUESTION: This command will not do anything with the values in the database with Members whose status is "1" ? no matter how many times i run this command right ?

Also, will running this command multiple time will do anything ? because all the members will be added in t he database who didn't pay so if someone run this command 2nd time, what will happen ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 17:01:14

It won't change or update rows with status = 1. Not because the status is 1, but because of the mere existence of the row. (The not exists clause says, "select something only if there are no qualifying rows")

If you run it multiple times, it will not insert repeatedly because the where clause will prevent such member+year combinations from being picked up.

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-08-12 : 10:22:08

thank you sunita, once again you saved the day !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources