Max Vlasov wrote:
> We all know UPDATE sometimes is limited so in order to make some complex
> conditional updating we can only rely on the complexity of WHERE clause.
>
> I would like to update my detail table based on master properties (so to set
> some value only if the corresponding master record fits some conditions).
> In case of only one property the statement can look like this
>
> UPDATE detail_table SET somefield=somevalue
> WHERE
> (SELECT masterfieldtocheck FROM master_table WHERE master_table.id
> =detail_table.masterid)=okvalue
>
> Is there a way to alias the master table when I need to check several fields
> of the master table? Or the only choice is to write
>
> WHERE
> ((SELECT masterfieldtocheck FROM master_table WHERE
> master_table.id=detail_table.masterid)=okvalue)
> AND
> ((SELECT masterfield2tocheck FROM master_table WHERE master_table.id
> =detail_table.masterid)=okvalue2)
where exists (
select 1 from master_table where
master_table.id=detail_table.masterid and
masterfieldtocheck = okvalue and
masterfield2tocheck = okvalue2);
-- or
where detail_table.masterid in (
select id from master_table where
masterfieldtocheck = okvalue and
masterfield2tocheck = okvalue2);
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
