Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
SQL UPDATE - PLEASE HELPI 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
Post Follow-up to this message
Re: SQL UPDATE - PLEASE HELP"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 --
Post Follow-up to this message Re: SQL UPDATE - PLEASE HELPHi 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 >
Post Follow-up to this message Re: SQL UPDATE - PLEASE HELPWhy 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
Post Follow-up to this message Re: SQL UPDATE - PLEASE HELPI 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
Post Follow-up to this message Re: SQL UPDATE - PLEASE HELPWell, because the UPDATE.. FROM .. syntax does not port and produces unpredictable results?
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
