?
Solved

Simple Update/Delete Issue for Multiple tables

Posted on 2005-05-09
13
Medium Priority
?
147 Views
Last Modified: 2013-12-24
Hello.  

My users can sign up to receive several publications (checkboxes). Then the user record is inserted once into the main table

Their id and the newsletter ids are stored in another table.

so if Dave Matthews, with npubUserID 2345 decides he wants to sign up for the following newsletters:

npubID      npub
4      Rock On Magazine      
21      World Concerns
88      Top Leaders

So, in the main table (npubUsers), his basic information will exists as:
npubUserID        Lname            email
2345                             Matthews                      dmathhews@aol.com

In the npubparts table,

npubpartsID      npubID      npubUserID  
76            4      2345
77            21      2345
78            88      2345

PROBLEM 1:
Sometimes I need to update or remove only one of the records in the pubparts table.  However, when I update, all records associated with the user are changed simultaneously.

MAJOR PROBLEM 2:
The other problem concerns deleting user records.

I made an update to this table recently.  Users who signed up only for World Concerns (21), were accidently included in the list for Top Leaders(88).  How can I select from pubparts only those users who signed up for World Concerns and Delete only their entries for Top Leaders?  

So, the structure would be the same as above.
pubpartsID      npubID      npubUserID  
76            4      2345
77            21      2345
78            88      2345

I tried the following (but I think I need a subquery within my query):

SELECT * FROM npubUsers
INNER JOIN npubparts
ON npubs.npubUserID=npubparts.npubUserID
INNER JOIN npubs
ON npubs.npubID=npubparts.npubID

Thanks.
0
Comment
Question by:synergeticsoul
  • 7
  • 5
13 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 13960882
Hopefully this is MS SQL... otherwise the below code will need to be modified slightly

Problem 2:

Are you saying only delete the TOp Leaders from people who also signed up for World Concerns?

DELETE FROM npubarts WHERE npubID = 88 AND npubUserID IN (SELECT DISTINCT npubUserID FROM npubarts WHERE npubID = 21)



Problem 1:

You need to specify the npubUserID   AND  the record ID that you want to update such as the npubID or the npubpartsID - not sure which one based on the description.
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13961783


{npub} Table
npubID  |   npub
---------------------------------------------
      04  |   Rock On Magazine    
      21  |   World Concerns
      88  |  Top Leaders


{npubparts} Table
npubpartsID  |   npubID  |   npubUserID  
------------------------------------------------
      76          |      4       |       2345
      77          |     21      |       2345
      78          |     88      |       2345


{npubUsers} Table
npubUserID   |    Lname     |     email
---------------------------------------------------
     2345        |  Matthews  | dmathhews@aol.com


I see you have a users table {npubUsers} and publications table {npub} and a table from which you get info from both based on what a user signs up for right, and this table is {npubparts} table.

so if you want to update their subscription status, all that is needed it updating this table etc.

If you are using MS SQL I'd highly recommend creating a stored procedure for handling updates and deletes to your table. But anyway, here we go. Two queries to look at... Update and Delete:

To Update:
-------------
<cfquery name="something" datasource="YourDSN">
  UPDATE npubparts
  SET npubID = '#FORM.UpdatedValue#'
  WHERE npubpartsID = #npubpartsID#
  AND npubUserID = #npubUserID#
</cfquery>

To Delete:
---------------
<cfquery name="something2" datasource="YourDSN">
  DELETE FROM npubparts
  WHERE
         npubpartsID = #npubpartsID#
  AND npubUserID = #npubUserID#
  AND npubID = #npubID#
</cfquery>

The more specific you are with identifying which record to delete or update the less trouble you'll have with deleting or updating info that you don't want to update of delete. The only table you want to update or delete on is the {npubparts} table, the others set ID numbers for this table. so don't play with them.

hope that helps...

~trail
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13961865
The only query you'd want to be joining with is when you're querying to populate your page with information, but not a query to change data in your tables, that's why you have your third table with info from both. If you want to get info from all your tables then do this...

<cfquery name="something3" datasource="YourDSN">
SELECT *
FROM npubUsers A
INNER JOIN npubparts B ON A.npubUserID  = B.npubUserID
INNER JOIN npub C ON B.npubID  = C.npubID
</cfquery>

This will give you all data from all three tables. But only use this to display your data because other wise when you make an update of delete, you're affecting the other tables as well.

~trail
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:synergeticsoul
ID: 13989533
The problem with the last query is that if I want to display the magazines for all users, I can't.

0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13991220
I'm a bit confused, why wouldn't you be able to select all user's magazines subscriptions? If you join all the tables, you get all the records, unless there's some records the joins aren't catching, is that the case?

~trail
0
 

Author Comment

by:synergeticsoul
ID: 13996138
No.  I made a mistake in the actual query (when I changed the table structure).

You mentioned that I should probably do this as a stored procedure.  

Would I do this as a transaction?  

I can post my cfml code (perhaps this is where I am going wrong and not the stored proc)
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13997683
yeah, posting your code would make it easier to see what's going on... ;o)
0
 

Author Comment

by:synergeticsoul
ID: 14027641
Here's my code for the update.  I would like to turn this into a transaction.  I am also a bit confused on the insert if the user wants another ezine.


<!--- Update User --->
<cfquery name="UpdateUser" datasource="#request.dsn#">
UPDATE allpubs
SET  email=<cfqueryparam value="#email#" cfsqltype="cf_sql_varchar">
WHERE pubid=#pubid#
</cfquery>

<cfelse>

<!--- Or Update User's Publications --->

<cfquery name="UpdatePubs" datasource="#request.dsn#">
UPDATE allpubsparts
SET ezineID=<cfqueryparam value=#ezineID#>,
parts_active=<cfqueryparam value="#parts_active#" cfsqltype="cf_sql_char">
WHERE allpubPartID=#allpubPartID#
</cfquery>

<!--- Or Insert New Publication for User, if user wants to be signed up for a new publication --->

--I am stuck here, only insert if user wants a new publication---

<cfquery name="insertNewPub" datasource="#request.dsn#">
INSERT INTO allpubs
(firstname,lastname, email, sdate, active)
VALUES
('#form.firstname#','#form.lastname#','#form.email#',#CreateODBCDateTime(now())#, 'Y')
</cfquery>

<cfquery name="getPubid" datasource="#request.dsn#">
select MAX (pubid) as LastID from allpubs </cfquery>

<cfloop list="#form.ezineid#" index="Formezineid">
<cfquery name="insertParts" datasource="#request.dsn#">
INSERT INTO allPubsParts
(pubid, ezineID, parts_active)
VALUES
('#getpubID.LastID#', '#Formezineid#', 'Y')
</cfquery>
</cfloop>
0
 

Author Comment

by:synergeticsoul
ID: 14060130
Hello.

I have increased the points in hopes that I could get an answer.  Quite simply, I just need to be able to insert new content (from above queries) if user wants more material (during the update process).  I am not sure on how to accomplish this.

Thanks.
0
 
LVL 20

Accepted Solution

by:
trailblazzyr55 earned 1400 total points
ID: 14061273

<cfquery name="UpdatePubs" datasource="#request.dsn#">

UPDATE allpubsparts
SET ezineID=<cfqueryparam value=#ezineID#>,
parts_active=<cfqueryparam value="#parts_active#" cfsqltype="cf_sql_char">
WHERE allpubPartID=#allpubPartID#

</cfquery>

looking at your update query what you need to do to update a users magazine subscription is do an update based on the user's ID. so something like this, this way you update thier profile in the {npubparts} Table, based on them.

<cfquery name="UpdatePubs" datasource="#request.dsn#">
UPDATE allpubsparts
SET ezineID = <CFQUERYPARAM CFSQLTYPE="CF_SQL_INTEGER" VALUE="#ezineID#">,  
parts_active = <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#allpubPartID#">  
WHERE npubUserID = '#npubUserID#'
</cfquery>

Give that a try, hope that helps
~trail
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 14061300
basically if you want to have specific transactions, follow this...

<cflock ...>
<cftransaction ...>

your queries here

</cftransaction>
</cflock>

~trail
0
 

Author Comment

by:synergeticsoul
ID: 14086124
Thank You!!!

0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 14088367
I'm glad we were able to help, let us know if you still have any questions ;o)

Thanks for the points ;o)

~trail
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month16 days, 4 hours left to enroll

850 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