Posted by David Portas on 09/29/94 11:29

"joshnosh" <me@u.com> wrote in message
news:28a2l1lrvh5pn8cul6nlvjc3acm3ivt9jl@4ax.com...
>I have two tables tblLogin and tblTrades
>
> I have 3 fields in each table, for thr purpose of this question
>
> tblLogin Fields are
> MemberNo
> RegNo
> Country
>
> tblTrades fields are
> MemberNo
> RegNo
> Country
>
> There are other fields in both table but the fields shown are fields i
> need to work on
>
> the Tables look like this
>
> tblLogin
> ----------------------------
> MemberNo | RegNo | Country |
> ----------------------------
> 1 1 Wales
> 2 1 england
> 3 1 scotland
> 4 2 wales
> 5 2 england
> 6 2 scotland
> 7 3 Wales
> 8 3 england
> 9 3 scotland
> 10 4 wales
> 11 4 england
> 12 4 scotland
>
> tblTrades
> ----------------------------
> MemberNo | RegNo | Country |
> ----------------------------
> 0 1 Wales
> 0 1 england
> 0 1 scotland
> 0 2 wales
> 0 2 england
> 0 2 scotland
> 0 3 Wales
> 0 3 england
> 0 3 scotland
> 0 4 wales
> 0 4 england
> 0 4 scotland
>
>
> I need the field MemberNo in tblTrades to match the MemberNo in
> tblLogin
> Any help appriciated as cant to get any SQL code i use to work,
> because it must be wrong.
> and its a lot of quesswork.
> I am using phpMyAdmin on a MYSql database
>

This is a Microsoft SQL Server forum so you'll probably get more help
elsewhere for MySQL. In standard SQL you can do this:

UPDATE tblTrades
SET memberno =
(SELECT memberno
FROM tblLogin
WHERE regno = tblTrades.regno
AND country = tblTrades.country) ;

assuming (regno,country) is unique in tblLogin.

--
David Portas
SQL Server MVP
--

[Back to original message]