Hi

I have a table 1 with postcodes and a flag field that indicates whether it
is welsh.

I have another table (table 2) which has a list of just the first 5
characters of welsh postcodes that I want to use as a lookup table and if
there is a match with the postcode field in table 1 I set the flag field in
table 1 to 1 - how can I formulate the query for this?

Thanks

jojo

here is update statement


update t1 set FlagColumn=1 from
table1 t1 inner join table2 t2 on t2.column=left(t1.postcode,5)

vinu


"jojo123" <jojo123@discussions.microsoft.com> wrote in message
news:0853A884-E9C7-46F1-BAE7-C9479E3FE2CB@microsoft.com...
> Hi
>
> I have a table 1 with postcodes and a flag field that indicates whether it
> is welsh.
>
> I have another table (table 2) which has a list of just the first 5
> characters of welsh postcodes that I want to use as a lookup table and if
> there is a match with the postcode field in table 1 I set the flag field
> in
> table 1 to 1 - how can I formulate the query for this?
>
> Thanks

UPDATE [table 1] SET flag=1 FROM
[table 1] t1 JOIN [table 2] t2 ON
SUBSTRING(t1,colname,1,5)=t2.colname
WHERE ........


"jojo123" <jojo123@discussions.microsoft.com> wrote in message
news:0853A884-E9C7-46F1-BAE7-C9479E3FE2CB@microsoft.com...
> Hi
>
> I have a table 1 with postcodes and a flag field that indicates whether it
> is welsh.
>
> I have another table (table 2) which has a list of just the first 5
> characters of welsh postcodes that I want to use as a lookup table and if
> there is a match with the postcode field in table 1 I set the flag field
> in
> table 1 to 1 - how can I formulate the query for this?
>
> Thanks

Thanks for that

Is it possible to update two tables in one update query? something like

update t1, t3 set FlagColumn=1 from
(table1 t1 inner join table2 t2 on t2.column=left(t1.postcode,5)) AND
(table3 t3 innerjoin table2 t2 on t2.column=left(t3.postcode,5))

"vinu" wrote:

>
>
> jojo
>
> here is update statement
>
>
> update t1 set FlagColumn=1 from
> table1 t1 inner join table2 t2 on t2.column=left(t1.postcode,5)
>
> vinu
>
>
> "jojo123" <jojo123@discussions.microsoft.com> wrote in message
> news:0853A884-E9C7-46F1-BAE7-C9479E3FE2CB@microsoft.com...
> > Hi
> >
> > I have a table 1 with postcodes and a flag field that indicates whether it
> > is welsh.
> >
> > I have another table (table 2) which has a list of just the first 5
> > characters of welsh postcodes that I want to use as a lookup table and if
> > there is a match with the postcode field in table 1 I set the flag field
> > in
> > table 1 to 1 - how can I formulate the query for this?
> >
> > Thanks
>
>
>

No. An update statement can only update one table.

Tom

"jojo123" <jojo123@discussions.microsoft.com> wrote in message
news:E7FAB0F0-9C95-42F0-99A5-09CA4BC4863F@microsoft.com...
> Thanks for that
>
> Is it possible to update two tables in one update query? something like
>
> update t1, t3 set FlagColumn=1 from
> (table1 t1 inner join table2 t2 on t2.column=left(t1.postcode,5)) AND
> (table3 t3 innerjoin table2 t2 on t2.column=left(t3.postcode,5))
>
> "vinu" wrote:
>
>>
>>
>> jojo
>>
>> here is update statement
>>
>>
>> update t1 set FlagColumn=1 from
>> table1 t1 inner join table2 t2 on t2.column=left(t1.postcode,5)
>>
>> vinu
>>
>>
>> "jojo123" <jojo123@discussions.microsoft.com> wrote in message
>> news:0853A884-E9C7-46F1-BAE7-C9479E3FE2CB@microsoft.com...
>> > Hi
>> >
>> > I have a table 1 with postcodes and a flag field that indicates whether
>> > it
>> > is welsh.
>> >
>> > I have another table (table 2) which has a list of just the first 5
>> > characters of welsh postcodes that I want to use as a lookup table and
>> > if
>> > there is a match with the postcode field in table 1 I set the flag
>> > field
>> > in
>> > table 1 to 1 - how can I formulate the query for this?
>> >
>> > Thanks
>>
>>
>>