Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: use UPDATE and SELECT statement together

    Hello.

    I want to make an UPDATE on my SELECT statement.

    This is the data I want to select:

    --------
    SELECT * FROM mens_fabric LEFT JOIN nwomen_fabric ON mens_fabric.fabric_title = nwomen_fabric.fabric_title WHERE nwomen_fabric.fabric_title = mens_fabric.fabric_title
    ------

    Once this data is selected, I am trying to now update the field mens_fabric.fabric_for to make it the value as 'both'

    Usually I can update all records by doing:

    UPDATE mens_fabric SET fabric_for = "both"


    How can I go about doing this? I don't know how to do both at the same time

    Thanks for amy help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, you cannot do a SELECT and an UPDATE in the same statement

    they are intrinsically different, and they must be separate statements

    if you want to do them "together" then perhaps you might want to create a transaction, but this is likely only in situations where the update needs to happen only on the rows which you selected, and before any other rows are inserted or updated...

    ... which seems quite unreasonable, given the example you've posted

    secondly, your LEFT OUTER JOIN will actually behave as an inner join, based on the existence of the WHERE clause...

    ... and the WHERE clause is redundant with your ON clause



    what's actually going on here?

    why do you need to select first? why not just go ahead and update the men's fabric to say 'both' when the titles are equal?

    and why do you have separate tables for men's and women's fabric? couldn't there just be one table with a designator for men's or women's or both?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    Thanks for your reply

    I have managed to do it like this:

    UPDATE men_fabric mf
    JOIN women_fabric wf
    ON wf.fabric_title = mf.fabric_title
    SET mf.fabric_for = 'both';


    However, I am now having a problem with DELETING data. I want to delete all records from table2, where it has the same value in fabric_title for table1

    I tried like this but have my syntax wrong

    DELETE women_fabric mf
    JOIN men_fabric wf
    ON wf.fabric_title = mf.fabric_title


    any ideas? and thanks again for your help

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DELETE wf
    FROM women_fabric AS wf
    JOIN men_fabric AS mf
    ON wf.fabric_title = mf.fabric_title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •