"YALTA" <gchenault@gmail.com> wrote in message
news:1183928182.399324.34770@n2g2000hse.googlegroups.com...
> Hello,
>
> I am new to working with SQL and I have a few questions. One the
> update query below, one will it work and I looking for a better
> understanding of the steps that are taking when this query runs.
>
> begin tran
>
> update address
> set phone = xxxxxxxxx,fax = yyyyyyyyyy
> from address y inner join
> (
> select phone, fax from tblmain m
> join tbl_address a on m.id = a.id
> where phone is null and fax is null
> )b join b.id = y.id
> where b.y.phone is null and y.fax is null
>
> commit tran
>
> I would think and correct me if I am wrong that these type queries
> first pull the data from the selected statement and then will update
> the join on the records returned?
>
> Any help would be great
>

<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> "YALTA" <gchena...@gmail.com> wrote in message
>
> news:1183928182.399324.34770@n2g2000hse.googlegroups.com...
>
>
>
> > Hello,
>
> > I am new to working with SQL and I have a few questions. One the
> > update query below, one will it work and I looking for a better
> > understanding of the steps that are taking when this query runs.
>
> > begin tran
>
> > update address
> > set phone = xxxxxxxxx,fax = yyyyyyyyyy
> > from address y inner join
> > (
> > select phone, fax from tblmain m
> > join tbl_address a on m.id = a.id
> > where phone is null and fax is null
> > )b join b.id = y.id
> > where b.y.phone is null and y.fax is null
>
> > commit tran
>
> > I would think and correct me if I am wrong that these type queries
> > first pull the data from the selected statement and then will update
> > the join on the records returned?
>
> > Any help would be great
>
> No it won't work. There are at least two syntax errors in it, but since it
> is only pseudo-code I can't be sure what the actual code is that you are
> going to run...
>
> Be aware that there is a serious flaw with the type of UPDATE statement you
> have used. If the join to the table ytou are updating from returns more than
> one row for any row in the table being updated then the result is undefined.
> You don't get an error message but the end results may not be what you
> expected. To avoid that problem you need to be sure that b.id is unique (a
> column which you unfortunately left out altogether!).
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --