Solved

MYSQL update query

Posted on 2014-01-27
5
445 Views
Last Modified: 2014-01-30
MySQL UPDATE QUERY

hello I need help with an update query in MYSQL

I have 2 tables
c_item_property   (TABLE NAME)                             c_document  (TABLE Name
     c_id   int                                        1 to 1                      c_id    int
     ref   int                                          1 to 1                      id    int
     visibility    tinyint(4)                                                     path    varchar

ok... so between the 2 tables I need to update the c_item_property table
with the 1 to 1 relationship (i think it might be a join.. not sure) between c_id .....and the ref to id  1 to 1 relationship.

I need to update visibility to  1      when path =  '/week_03'

thanks for your help

FaithDan
0
Comment
Question by:FaithDan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 39813334
this article will explain how you need to write your UPDATE , though you must check out which syntax you need to pick for MySQL:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
or directly the mysql reference for UPDATE:
http://dev.mysql.com/doc/refman/5.0/en/update.html
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39813337
in code, it might be something like this  
UPDATE c_item_property   ip, c_document  c
  SET ip.visibility = 1
WHERE ip.id = c.id
  AND c.path =  '/week_03'

Open in new window

0
 
LVL 1

Author Comment

by:FaithDan
ID: 39813730
Hello .. I have it working as below in a select query..

what do I need to do to the code to change it to the update query.  I need to update ip.visibility to 1

SELECT ip.visibility, c.path
FROM c_item_property ip
join c_document c on ip.c_id = c.c_id
AND ip.ref = c.id and (ip.c_id = 62 or ip.c_id = 63 or ip.c_id = 64)

WHERE

 c.title LIKE 'week 03'
LIMIT 0 , 90
0
 
LVL 41

Accepted Solution

by:
Sharath earned 300 total points
ID: 39814291
check this.
UPDATE  c_item_property ip, c_document c
         SET  ip.visibility = 1
 WHERE ip.c_id = c.c_id
        AND ip.ref = c.id
        AND ip.c_id IN (62,63,64)
        AND c.title LIKE 'week 03’
    LIMIT 0,90

Open in new window

0
 
LVL 1

Author Closing Comment

by:FaithDan
ID: 39821495
Thank you very much.  This worked perfecly
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question