Microsoft SQL Server forum - SQL UPDATE

Author

SQL UPDATE - PLEASE HELP

joshnosh

2005-10-27, 9:24 am

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

2005-10-27, 9:24 am

"joshnosh" <me@u.com> wrote in message
news:28a2l1lrvh5pn8c
ul6nlvjc3acm3ivt9jl@
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
--

John Bell

2005-10-27, 9:24 am

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:28a2l1lrvh5pn8c
ul6nlvjc3acm3ivt9jl@
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
>

ZeldorBlat

2005-10-27, 9:24 am

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

2005-10-27, 9:24 am

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:
> 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

--CELKO--

2005-10-27, 9:24 am

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

Sponsored Links