Update with a Join?

Hello everyone,

I have 2 tables.  Lets call them audit and docs.

audit contains a list of docid's with a time/date stamp
docs contains the same list of docid's and also contains a flag field.

I want to update docs.flag to 0 where audit.timestamp is equal to a date.

Both tables contain docid.

I assume this is a join, I'm just not sure how to do it, can anybody assist please?

Kind regards,

Darren Bridle
dbridleAsked:
Who is Participating?
 
Aaron TomoskySD-WAN SimplifiedCommented:
Update docs set flag  = 0 where docid in (select docid from audit where timestamp = 'date')
0
 
mwiercinCommented:
Hi!

This will do:

UPDATE 
  docs d,
  JOIN audit a USING (docid) 
SET 
  d.flag = 0
WHERE
   a.timestamp = '2011-11-11 11:11:11'

Open in new window


Btw. It's always a good idea to rewrite that query to be a SELECT before you run it, just to make sure it will update what you actually want.
0
 
dbridleAuthor Commented:
Assuming I use the update join, this is the query:

UPDATE
    p2.neocol_document_extract_list d,
    JOIN p2.neocol_audit a USING (f_docnumber)
SET
    d.import_flag = 0
WHERE
    a.importdate > '2011-02-08 00:00:00'
and
    a.importdate < '2011-02-08 23:59:00';

There is an error at the JOIN part of the query:

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN p2.neocol_audit a USING (f_docnumber)
WHERE
    a.importdate > '2011-02-08' at line 3
0
 
mwiercinCommented:
It's because of the comma after p2.neocol_document_extract_list d, my example was flawed :)

Try

UPDATE 
    p2.neocol_document_extract_list 
    JOIN p2.neocol_audit a USING (f_docnumber)
SET 
    d.import_flag = 0
WHERE
    a.importdate > '2011-02-08 00:00:00' 
and 
    a.importdate < '2011-02-08 23:59:00';

Open in new window

0
 
dbridleAuthor Commented:
I went ahead and used Update docs set flag  = 0 where docid in (select docid from audit where timestamp = 'date') and it worked a treat.

Thank you both for your help, I know 2 ways to do that now!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.