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!

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?

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

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