Subquery behavior

Hi All,

I'm a bit of a newbie, so any help would be appreciated. I'm trying to
update a table, but each row in the table has a "deleted" flag which
is stored in another table, linked by an ID. So, I need to write
something like this...

UPDATE vtiger_potential SET closingdate = "2016-01-15"
WHERE closingdate = "0000-00-00"
AND potentialid IN (
SELECT vtiger_crmentity.crmid
FROM vtiger_crmentity
INNER JOIN vtiger_potential on vtiger_crmentity.crmid =
vtiger_potential.potentialid
WHERE vtiger_crmentity.deleted=0
)

I hope it's fairly clear what I'm trying to do here. In plain English,
I guess I would say I want to create a list of IDs that aren't
deleted, then update the rows with those IDs - but I can't do it how I
thought it would be done, because I can't reference the table I'm
updating in the subquery. Am I even thinking about this in the right
way? How can I go about this?

TIA,

Russ.

Redkins wrote:
> Hi All,
>
> I'm a bit of a newbie, so any help would be appreciated. I'm trying to
> update a table, but each row in the table has a "deleted" flag which
> is stored in another table, linked by an ID. So, I need to write
> something like this...
>
> UPDATE vtiger_potential SET closingdate = "2016-01-15"
> WHERE closingdate = "0000-00-00"
> AND potentialid IN (
> SELECT vtiger_crmentity.crmid
> FROM vtiger_crmentity
> INNER JOIN vtiger_potential on vtiger_crmentity.crmid =
> vtiger_potential.potentialid
> WHERE vtiger_crmentity.deleted=0
> )
>
> I hope it's fairly clear what I'm trying to do here. In plain English,
> I guess I would say I want to create a list of IDs that aren't
> deleted, then update the rows with those IDs - but I can't do it how I
> thought it would be done, because I can't reference the table I'm
> updating in the subquery. Am I even thinking about this in the right
> way? How can I go about this?
>
> TIA,
>
> Russ.


Would something like this work:
UPDATE vtiger_potential p SET v.closingdate = "2016-01-15"
INNER JOIN vtiger_crmentity c ON
(p.potentialid = c.crmid)
WHERE c.deleted=0 AND p.closingdate = "0000-00-00"

sheldonlg wrote:
> Redkins wrote:
>> Hi All,
>>
>> I'm a bit of a newbie, so any help would be appreciated. I'm trying to
>> update a table, but each row in the table has a "deleted" flag which
>> is stored in another table, linked by an ID. So, I need to write
>> something like this...
>>
>> UPDATE vtiger_potential SET closingdate = "2016-01-15"
>> WHERE closingdate = "0000-00-00"
>> AND potentialid IN (
>> SELECT vtiger_crmentity.crmid
>> FROM vtiger_crmentity
>> INNER JOIN vtiger_potential on vtiger_crmentity.crmid =
>> vtiger_potential.potentialid
>> WHERE vtiger_crmentity.deleted=0
>> )
>>
>> I hope it's fairly clear what I'm trying to do here. In plain English,
>> I guess I would say I want to create a list of IDs that aren't
>> deleted, then update the rows with those IDs - but I can't do it how I
>> thought it would be done, because I can't reference the table I'm
>> updating in the subquery. Am I even thinking about this in the right
>> way? How can I go about this?
>>
>> TIA,
>>
>> Russ.
>
>
> Would something like this work:
> UPDATE vtiger_potential p SET v.closingdate = "2016-01-15"
> INNER JOIN vtiger_crmentity c ON
> (p.potentialid = c.crmid)
> WHERE c.deleted=0 AND p.closingdate = "0000-00-00"

Make that
UPDATE vtiger_potential p
INNER JOIN vtiger_crmentity c ON
(p.potentialid = c.crmid)
SET v.closingdate = "2016-01-15"
WHERE c.deleted =0 AND p.closingdate = "0000-00-00"

That worked perfectly. I actually tried to do an inner join but I must
have got my statement order messed up as I kept getting syntax errors.
Thank you very much.