0

I've got a select query I'm using to pick out contacts in my DB that haven't been spoken to in a while. I'd like to run an INSERT query to enter in a duplicate note for all the records that are returned with this select query... problem is I'm not exactly sure how to do it.

The SELECT query itself is likely a bit of a convoluted mess. I basically want to have the most recent note from each partner selected, then select ONLY partners that haven't got a note from a certain date and back... the SELECT query goes:

SELECT * FROM
(
SELECT * FROM
(
SELECT
partners.partners_id,
partners.CompanyName,
notes.Note,
notes.DateCreated
FROM
notes
JOIN
partners ON notes.partners_id = partners.partners_id
ORDER BY notes.DateCreated DESC
) AS Part1
GROUP BY partners_id
ORDER BY DateCreated ASC
) AS Part2
WHERE
DateCreated <= '2013-01-15'

How would a run an INSERT query that would only go into the same records as this SELECT?

The insert would enter records such as:

INSERT INTO notes
(
notes_id,
partners_id,
Note,
CreatedBy,
DateCreated
)
SELECT
UUID(),
partners.partners_id,
'Duplicated message!',
'User',
'2013-02-14'
FROM
partners
Benshack
  • 137
  • 5

1 Answers1

0

If you want to do this all in SQL, you could use an UPDATE statement.

UPDATE tablename SET note='duplicate' where id in ( your statement here);

Note that in order for this to work 'id' needs to be a column from 'tablename'. Then, your statement has to return a single column, not *. The column returned needs to be the id that will let your update statement know which rows to update in 'tablename'.

aglassman
  • 2,643
  • 1
  • 17
  • 30
  • I've updated my question a little bit - would it be possible to use UPDATE to enter in the records I'd like to as shown in my example? – Benshack Feb 14 '13 at 22:47
  • I think what you'd want to use in your case is the INSERT INTO statement. Here is a question that may help: http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from – aglassman Feb 14 '13 at 23:04
  • I already know how to do INSERT INTO statements, however I can't seem o figure out how to get the INSERT INTO statment above to function inside of the results of that SELECT statement. Thanks for the help. – Benshack Feb 14 '13 at 23:20
  • I just figured it out (and I feel like a retard) hahah! I was just not properly putting in the second part of the statement! – Benshack Feb 15 '13 at 16:15
  • Haha, yeah, it's always something simple. I've had many of these face palm moments. http://picardfacepalm.com/ – aglassman Feb 15 '13 at 16:24