Simple Update/Delete Issue for Multiple tables

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.

{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

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

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

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

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)

yeah, posting your code would make it easier to see what's going on... ;o)

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>

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.

basically if you want to have specific transactions, follow this...

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

your queries here

</cftransaction>
</cflock>

~trail

Thank You!!!

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

Thanks for the points ;o)

~trail