Re: Szybkie wypełnianie zerami tabeli

Article View: pl.comp.bazy-danych

Article #195200

Back to pl.comp.bazy-danych Previous Next

#195200

From: Paweł Broda
Date: Fri, 20 Jan 2017 12:18

41 lines
2687 bytes

W dniu piątek, 20 stycznia 2017 12:03:13 UTC+1 użytkownik Borneq napisał:
> W dniu 20.01.2017 o 11:47, Cezary Grądys pisze:
> > SELECT * FROM x;
> > test=> WITH RECURSIVE x AS
> > (
> >   SELECT 1 AS a, 0 AS b, 0 AS c
> >   UNION ALL
> >   SELECT 1+a, b, c FROM x WHERE a<100000
> > )
> > SELECT * FROM x;
> >
> > Na postgresie działa
> 
> Tu mamy dwa selecty do sprawdzenia i polecenie? Jak się robi polecenie z 
> "test=>"
> u mnie wygląda to tak:
> 	MySqlDB db("127.0.0.1", "root", "rootpass", "bitcoin");
> 	string stmt;
> 	stmt += "test = > WITH RECURSIVE x AS\n";
> 	stmt += "(\n";
> 	stmt += "SELECT 1 AS a, 0 AS b, 0 AS c\n";
> 	stmt += "UNION ALL\n";
> 	stmt += "SELECT 1 + a, b, c FROM x WHERE a<100000\n";
> 	stmt += ")\n";
> 	db.execute(stmt);
> 
> i coś tu nie gra.
> To jest procedura , jak się obsługuje odwrotnie od zwykłych statement?

To jest jeden select.

W ANSI SQL (i np. w Oracle) jest polecenie SQL MERGE. Z tego co wyczytałem to MySQL tego nie obsługuje - ale możesz spróbować tak:

INSERT IGNORE INTO TwojaTabela (a, b, c)
WITH RECURSIVE x AS
(
   SELECT 1 AS a, 0 AS b, 0 AS c
   UNION ALL
   SELECT 1+a, b, c FROM x WHERE a<100000
)
SELECT a, b, c FROM x

Technical Details

Message-ID: <0f16dcc8-0ab3-4088-af47-631a53a81da8@googlegroups.com>

Path:

mbox-mo1.pugleaf.net!news-archive.icm.edu.pl!news.icm.edu.pl!newsfeed.pionier.net.pl!news.glorb.com!r185no840134ita.0!news-out.google.com!h135ni13582ith.0!nntp.google.com!r185no840129ita.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail

References:

<o5sn7j$9es$1@node1.news.atman.pl> <bfee7e61-5c91-4ecb-9bf4-58669fd86424@googlegroups.com> <o5sniu$9n7$1@node1.news.atman.pl> <e6fc45bd-6dc5-4c69-9e4b-42f7723e5351@googlegroups.com> <5881eac8$0$15207$65785112@news.neostrada.pl> <o5sqph$d1h$1@node1.news.atman.pl>

Show Full Headers

Complete Article Headers

X-Received: by 10.129.128.68 with SMTP id q65mr4384253ywf.135.1484914686445;        Fri, 20 Jan 2017 04:18:06 -0800 (PST)
X-Received: by 10.157.35.89 with SMTP id k25mr1432526otd.11.1484914686400; Fri, 20 Jan 2017 04:18:06 -0800 (PST)
Path: mbox-mo1.pugleaf.net!news-archive.icm.edu.pl!news.icm.edu.pl!newsfeed.pionier.net.pl!news.glorb.com!r185no840134ita.0!news-out.google.com!h135ni13582ith.0!nntp.google.com!r185no840129ita.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: pl.comp.bazy-danych
Date: Fri, 20 Jan 2017 04:18:05 -0800 (PST)
In-Reply-To: <o5sqph$d1h$1@node1.news.atman.pl>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=194.0.240.206; posting-account=fKvxEAoAAAAaXZSkKFQkID0nr0tgYJgq
NNTP-Posting-Host: 194.0.240.206
References: <o5sn7j$9es$1@node1.news.atman.pl> <bfee7e61-5c91-4ecb-9bf4-58669fd86424@googlegroups.com> <o5sniu$9n7$1@node1.news.atman.pl> <e6fc45bd-6dc5-4c69-9e4b-42f7723e5351@googlegroups.com> <5881eac8$0$15207$65785112@news.neostrada.pl> <o5sqph$d1h$1@node1.news.atman.pl>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <0f16dcc8-0ab3-4088-af47-631a53a81da8@googlegroups.com>
Subject: Re: Szybkie wypełnianie zerami tabeli
From: Paweł Broda <pawel.a.broda@gmail.com>
Injection-Date: Fri, 20 Jan 2017 12:18:06 +0000
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Reply to Parent Message