On Sat, 2015-10-10 at 14:11 +0700, Mogens Melander wrote:
> From the manual:
>
> 11.2.6. REPLACE Syntax
>
> REPLACE [LOW_PRIORITY | DELAYED]
> [INTO] tbl_name [(col_name,...)]
> VALUES ({expr | DEFAULT},...),(...),...
>
> Or:
>
> REPLACE [LOW_PRIORITY | DELAYED]
> [INTO] tbl_name
> SET col_name={expr | DEFAULT}, ...
>
> Or:
>
> REPLACE [LOW_PRIORITY | DELAYED]
> [INTO] tbl_name [(col_name,...)]
> SELECT ...
>
> REPLACE works exactly like INSERT, except that if an old row in the
> table has the same value as a new row for a PRIMARY KEY or a UNIQUE
> index, the old row is deleted before the new row is inserted. See
> Section 11.2.4, “INSERT Syntax”.
>
> REPLACE is a MySQL extension to the SQL standard. It either inserts, or
> deletes and inserts. For another MySQL extension to standard SQL — that
> either inserts or updates — see Section 11.2.4.3, “INSERT ... ON
> DUPLICATE KEY UPDATE Syntax”.
>
> Note that unless the table has a PRIMARY KEY or UNIQUE index, using a
> REPLACE statement makes no sense. It becomes equivalent to INSERT,
> because there is no index to be used to determine whether a new row
> duplicates another.
>
>
>
> On 2015-10-09 03:45, Ashley Sheridan wrote:
> > Hi All,
> >
> > This is a bit of an odd one, and while it's largely an SQL problem, it
> > will have PHP logic because SQL just doesn't have quite the syntax.
> >
> > I'm sure this is a standard problem that has a standard solution, but
> > as
> > of yet, I've not come across said solution.
> >
> > I need to update a series of data in a DB. Some of it may exist
> > already,
> > and some may need to be removed.
> >
> > Now the typical for updating (but not removing) data and adding new is
> > the familiar:
> >
> > INSERT ... ON DUPLICATE KEY UPDATE ...
> >
> > syntax, but what about those bits which might need removing?
> >
> > What's the typical solution that any of you use? I normally just empty
> > all rows of data sharing the common id for the set of data and then
> > insert new rows, but that seems wasteful to me, and less clean than it
> > ought to be.
> >
> > Thanks,
> > Ash
> >
> > http://www.ashleysheridan.co.uk
> >
> >
> >
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
>
> --
> Mogens
> +66 8701 33224
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
Hi Mogens,
I dismissed that mainly because ON DUPLICATE KEY UPDATE syntax does that
anyway, but allows me to retain the primary keys.
Also, REPLACE wouldn't solve the deleting extra records issue (e.g.
where the db contains 10 and I'm only updating with 8, so 2 would need
to go). Thank you for the help though.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
