Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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
0
dbridle
Asked:
dbridle
  • 2
  • 2
2 Solutions
 
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
 
Aaron TomoskySD-WAN SimplifiedCommented:
Update docs set flag  = 0 where docid in (select docid from audit where timestamp = 'date')
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now