SQL UPDATE - PLEASE HELP

[joshnosh]

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

[David Portas]

Re: SQL UPDATE - PLEASE HELP

"joshnosh" <me@u.com> wrote in message
news:28a2l1lrvh 5pn8cul6nlvjc3a cm3ivt9jl@4ax.c om...[color=blue]
>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
>[/color]

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.count ry) ;

assuming (regno,country) is unique in tblLogin.

--
David Portas
SQL Server MVP
--

[John Bell]

Re: SQL UPDATE - PLEASE HELP

Hi

You could use a number table http://www.aspfaq.com/show.asp?id=2516 such as

CREATE TABLE tblLogin (
MemberNo int,
RegNo int,
Country char(10) )

CREATE TABLE tblTrades (
MemberNo int,
RegNo int,
Country char(10))

INSERT INTO tblLogin ( MemberNo, RegNo, Country )
SELECT 1, 1, 'Wales'
UNION ALL SELECT 2, 1, 'england'
UNION ALL SELECT 3, 1, 'scotland'
UNION ALL SELECT 4, 2, 'wales'
UNION ALL SELECT 5, 2, 'england'
UNION ALL SELECT 6, 2, 'scotland'
UNION ALL SELECT 7, 3, 'Wales'
UNION ALL SELECT 8, 3, 'england'
UNION ALL SELECT 9, 3, 'scotland'
UNION ALL SELECT 10, 4, 'wales'
UNION ALL SELECT 11, 4, 'england'
UNION ALL SELECT 12, 4, 'scotland'

INSERT INTO tblTrades ( MemberNo , RegNo , Country )
SELECT 0, 1, 'Wales'
UNION ALL SELECT 0, 1, 'england'
UNION ALL SELECT 0, 1, 'scotland'
UNION ALL SELECT 0, 2, 'wales'
UNION ALL SELECT 0, 3, 'scotland'
UNION ALL SELECT 0, 4, 'wales'
UNION ALL SELECT 0, 4, 'england'
UNION ALL SELECT 0, 4, 'scotland'
UNION ALL SELECT 0, 2, 'england'
UNION ALL SELECT 0, 2, 'scotland'
UNION ALL SELECT 0, 3, 'Wales'
UNION ALL SELECT 0, 3, 'england'

UPDATE t
SET MemberNo = c.MemberNo
FROM ( SELECT 1 AS MemberNo
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12 ) C
CROSS JOIN tblTrades t
JOIN tblLogin l on l.RegNo = t.RegNo AND l.Country = t.Country AND
c.MemberNo = l.MemberNo

SELECT * FROM tblTrades order by memberno

John

"joshnosh" <me@u.com> wrote in message
news:28a2l1lrvh 5pn8cul6nlvjc3a cm3ivt9jl@4ax.c om...[color=blue]
>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
>[/color]

[ZeldorBlat]

Re: SQL UPDATE - PLEASE HELP

Why would you do all that?

upate tblTrades
set MemberNo = l.MemberNo
from tblTrades t, tblLogin l
where t.RegNo = l.RegNo
and t.Country = l.Country

[John Bell]

Re: SQL UPDATE - PLEASE HELP

I don't know... I miss-read the data and if it was how it thought it
was it wouldn't have worked anyhow!!!

John

ZeldorBlat wrote:[color=blue]
> Why would you do all that?
>
> upate tblTrades
> set MemberNo = l.MemberNo
> from tblTrades t, tblLogin l
> where t.RegNo = l.RegNo
> and t.Country = l.Country[/color]

[--CELKO--]

Re: SQL UPDATE - PLEASE HELP

Well, because the UPDATE.. FROM .. syntax does not port and produces
unpredictable results?