A Broadband and ADSL forum. BroadbanterBanter

Welcome to BroadbanterBanter.

You are currently viewing as a guest which gives you limited access to view most discussions and other FREE features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload your own photos and access many other special features. Registration is fast, simple and absolutely free so please, join our community today.

Go Back   Home » BroadbanterBanter forum » Newsgroup Discussions » uk.telecom.broadband (UK broadband)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

uk.telecom.broadband (UK broadband) (uk.telecom.broadband) Discussion of broadband services, technology and equipment as provided in the UK. Discussions of specific services based on ADSL, cable modems or other broadband technology are also on-topic. Advertising is not allowed.

query problem.



 
 
Thread Tools Display Modes
  #1  
Old February 25th 14, 05:54 AM posted to uk.telecom.broadband
The Natural Philosopher
external usenet poster
 
Posts: 2,728
Default query problem.

say I have to tables
a and b

a has fields i,j
b has fields x,y

I want to set b.x to a.i when a.j=b.y

will

update a,b set b.x=a.i where a.j=b.y

work??

B has over a million records..so I want to make sure its right before
starting what will be a LONG haul.


--
Ineptocracy

(in-ep-toc'-ra-cy) - a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.

  #2  
Old February 25th 14, 07:54 AM posted to uk.telecom.broadband
Graham J
external usenet poster
 
Posts: 620
Default query problem.

The Natural Philosopher wrote:
say I have to tables
a and b

a has fields i,j
b has fields x,y

I want to set b.x to a.i when a.j=b.y

will

update a,b set b.x=a.i where a.j=b.y

work??

B has over a million records..so I want to make sure its right before
starting what will be a LONG haul.


Make a small table with the same data structure and test it ...???

--
Graham J

  #3  
Old February 25th 14, 09:35 AM posted to uk.telecom.broadband
Mark
external usenet poster
 
Posts: 525
Default query problem.

On Tue, 25 Feb 2014 05:54:46 +0000, The Natural Philosopher
wrote:

say I have to tables
a and b

a has fields i,j
b has fields x,y

I want to set b.x to a.i when a.j=b.y

will

update a,b set b.x=a.i where a.j=b.y

work??

B has over a million records..so I want to make sure its right before
starting what will be a LONG haul.


AFAIK you SQL should work. A guru we have here has suggested an
alternative:

UPDATE b SET b.x = a.i FROM b INNER JOIN a ON b.y = a.j

--
(\__/) M.
(='.'=) If a man stands in a forest and no woman is around
(")_(") is he still wrong?

  #4  
Old February 25th 14, 10:56 AM posted to uk.telecom.broadband
Allan
external usenet poster
 
Posts: 61
Default query problem.

On 25/02/2014 05:54, The Natural Philosopher wrote:
say I have to tables
a and b

a has fields i,j
b has fields x,y

I want to set b.x to a.i when a.j=b.y

will

update a,b set b.x=a.i where a.j=b.y

work??

B has over a million records..so I want to make sure its right before
starting what will be a LONG haul.



shouldn't this be on comp.databases ... ? :-)
  #5  
Old February 25th 14, 11:30 AM posted to uk.telecom.broadband
Adrian C
external usenet poster
 
Posts: 440
Default query problem.

On 25/02/2014 05:54, The Natural Philosopher wrote:
say I have to tables
a and b

a has fields i,j
b has fields x,y

I want to set b.x to a.i when a.j=b.y

will

update a,b set b.x=a.i where a.j=b.y

work??

B has over a million records..so I want to make sure its right before
starting what will be a LONG haul.



What are the field data types involved?
Are there any predefined indexes for the field columns compared?

--
Adrian C

  #6  
Old February 25th 14, 02:03 PM posted to uk.telecom.broadband
The Natural Philosopher
external usenet poster
 
Posts: 2,728
Default query problem.

On 25/02/14 11:30, Adrian C wrote:
On 25/02/2014 05:54, The Natural Philosopher wrote:
say I have to tables
a and b

a has fields i,j
b has fields x,y

I want to set b.x to a.i when a.j=b.y

will

update a,b set b.x=a.i where a.j=b.y

work??

B has over a million records..so I want to make sure its right before
starting what will be a LONG haul.



What are the field data types involved?
Are there any predefined indexes for the field columns compared?

Sorry - comp.databases.mysql is next to this group on my subsrciption list.
j and y are strings
x and i are ntergers

indexes will be added before this once and for all update ;-0)

Its basically a 'flat' database that needs to be put back in relational
form.

All values of b.y need to go in a new table a, and references created
for them...


--
Ineptocracy

(in-ep-toc'-ra-cy) - a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.

  #7  
Old February 26th 14, 08:37 AM posted to uk.telecom.broadband,comp.databases.mysql
The Natural Philosopher
external usenet poster
 
Posts: 2,728
Default query problem.

On 25/02/14 14:03, The Natural Philosopher wrote:
On 25/02/14 11:30, Adrian C wrote:
On 25/02/2014 05:54, The Natural Philosopher wrote:
say I have to tables
a and b

a has fields i,j
b has fields x,y

I want to set b.x to a.i when a.j=b.y

will

update a,b set b.x=a.i where a.j=b.y

work??

B has over a million records..so I want to make sure its right before
starting what will be a LONG haul.



What are the field data types involved?
Are there any predefined indexes for the field columns compared?

Sorry - comp.databases.mysql is next to this group on my subsrciption list.
j and y are strings
x and i are ntergers

indexes will be added before this once and for all update ;-0)

Its basically a 'flat' database that needs to be put back in relational
form.

All values of b.y need to go in a new table a, and references created
for them...


Note to self, and others.

As with the delights of regexps, the time taken to find the correct SQL
command plus the time to execute it comfortably exceeded the time it
took to write a C program to do it step by step and let me know what it
was doing..

AND it didn't bring the server to a shuddering halt with its I/O limits
exceeded...

When all you have is a dbm, everything looks like a 500 Mbyte temporary
table it seems..

But I now have 'normalised' data: yayy!
All 2.something million records of it.




--
Ineptocracy

(in-ep-toc'-ra-cy) - a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.

 




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sky query Woody uk.telecom.broadband (UK broadband) 7 April 8th 08 07:31 PM
Sky BB MAX query Marauder uk.telecom.broadband (UK broadband) 11 March 24th 07 12:12 PM
query Martinpara uk.comp.home-networking (UK home networking) 7 December 6th 06 11:29 PM
NTL query Cuprager uk.telecom.broadband (UK broadband) 7 January 22nd 06 09:18 AM
NIC Query Paul Felton uk.telecom.broadband (UK broadband) 4 April 24th 04 06:12 PM


All times are GMT +1. The time now is 03:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.Content Relevant URLs by vBSEO 2.4.0
Copyright 2004-2019 BroadbanterBanter.
The comments are property of their posters.