Multiple MySQL Insert statement for relational tables

Report · Jan 11, 2007

Running on PHP but anyone with good knowledge of SQL/MySQL can help.

I have two tables - Contacts and Contact Notes. Contacts has a Unique ID, as does Contact Notes but Contact Notes also has a column to link each record to a record in the Contacts Table - all fairly simple relational database stuff so far.

I have just completed a mailmerge (mailshot) to 300 records that have been marked in the database and I would like to be able to add a note to every individual record identifying that this has been done. Obviously doing this for each one would be very time consuming.

The statement would be something like this:

INSERT INTO CONTNOTES (NOTECONTACTID,CREATEDATE,NOTE) VALUES ('????????','2007-01-11','Mailshot sent')
INNER JOIN CONTNOTES ON CONTACTS.CONTACTID = CONTNOTES.NOTESCONTACTID
WHERE CONTACTS.MERGECODE = 'Marked for mailshot'

Firstly, do I need to the innerjoin section and secondly, what the hell do I put in the ?????? part?

The Unique ID for each record in both tables is an autoincrement so I don't need to worry about that. It's the link ID insert value I can't get my head round.

Is this even possible?

Report · Jan 12, 2007

It would be great if someone knew a little abou this - any ideas?

Report · Jan 12, 2007

.oO(RichardODreamweaver)

> I have just completed a mailmerge (mailshot) to 300 records that have been
>marked in the database and I would like to be able to add a note to every
>individual record identifying that this has been done. Obviously doing this
>for each one would be very time consuming.
>
> The statement would be something like this:
>
> INSERT INTO CONTNOTES (NOTECONTACTID,CREATEDATE,NOTE) VALUES
>('????????','2007-01-11','Mailshot sent')
> INNER JOIN CONTNOTES ON CONTACTS.CONTACTID = CONTNOTES.NOTESCONTACTID
> WHERE CONTACTS.MERGECODE = 'Marked for mailshot'
>
> Firstly, do I need to the innerjoin section and secondly, what the hell do I
>put in the ?????? part?
>
> The Unique ID for each record in both tables is an autoincrement so I don't
>need to worry about that. It's the link ID insert value I can't get my head
>round.
>
> Is this even possible?

The easiest way would be to do it with PHP: First grab the IDs of all
marked records, then in a second step loop through these IDs and insert
the comments.

It might also be possible to do it with SQL alone and an
INSERT ... SELECT ... statement.

Micha

Report · Jan 12, 2007

Hmm OK - not experienced with looping.

I've got all the ID's from the marked records in the first table in a recordset but don't know how you then do a loop.

Presumably, the loop would have to identify which record to start with and move incrementally through every record in the primary recordset.