SQL Question

[ShiBDiB]

Trying to get an event that deletes a table than repopulates it..

The event I have is

DELETE FROM services.corproster;
REPLACE INTO services.corproster (name)
SELECT name FROM seat.corporation_member_tracking;


It doesn't run tho. If I run the DELETE FROM and REPLACE INTO separate it works but that is kinda messy. How do I make this work or what's a better solution?

 

[ShiBDiB]

bump still haven't found a better solution than splitting it

 

[Aquinus]

REPLACE INTO only DELETEs when there is already a match in the table, so after the delete, it means nothing but it won't remove the old names that don't exist anymore. You almost are talking about a DELETE where you're JOINing on something not in another table, which is tricky to do even with a half-capable database. What you're describing to the best of my knowledge can't be done in a single query in MySQL. I have trouble even figuring out how that would get done in PostgreSQL.

I would do practically what you're doing now, just plop it into a transaction (if INNODB,) and do TRUNCATE instead of DELETE which will reset the primary key as well IIRC. I tend to work in PostgreSQL so I sometimes get features between the two confused.

START TRANSACTION;
  TRUNCATE TABLE services.corproster;
  INSERT INTO services.corproster (name) SELECT name FROM seat.corporation_member_tracking;
COMMIT;

 

[ShiBDiB]

START TRANSACTION;
DELETE FROM services.corproster;
REPLACE INTO services.corproster (name)
SELECT name FROM seat.corporation_member_tracking;
COMMIT;

works if I run it on its own. But it throws errors if I try to make it an event.

edit

Adding

BEGIN .... END worked.

 

[Easy Rhino]

Are you writing just straight SQL

Trying to get an event that deletes a table than repopulates it..

Why?

 

[ShiBDiB]

Are you writing just straight SQL


Why?

Because I couldn't find another way to update a table correctly. Everything else didn't remove entries that didn't exist in the table I was pulling from.

BEGIN
START TRANSACTION;
DELETE FROM services.corproster;
REPLACE INTO services.corproster (name)
SELECT name FROM seat.corporation_member_tracking;
COMMIT;
END

works

 

[Aquinus]

Because I couldn't find another way to update a table correctly. Everything else didn't remove entries that didn't exist in the table I was pulling from.

Why not just use CREATE TRIGGER to update the target table as changes occur? Rebuilding database tables tend to be a costly action.

Second: Is there any reason why this can't be a database view?

 

[ShiBDiB]

Why not just use CREATE TRIGGER to update the target table as changes occur?

Because I have no idea how to do SQL besides what I'm learning from stackoverflow

 

[Aquinus]

Because I have no idea how to do SQL besides what I'm learning from stackoverflow

You might want to start with what you're trying to do rather than how you're trying to do it. The context isn't clear to me.

I see a chance to make a view:

CREATE VIEW foo AS SELECT name FROM seat.corporation_member_tracking;

Then you can just select the view:

SELECT name FROM foo;

Is this table just a list of the state of another table or does it store more information? Can we get more scope-related information?

 

[dom99]

You want to use a MERGE statement

 

[brettjv]

It makes no sense to use replace into because a)you're truncating the table first, and b) you only have one field anyways. with an empty table it's synonymous with insert into. it will work of course, but might be slightly higher overhead. But I'm just telling you so you understand ... it's meant to insert any new rows, ignore existing/same rows, and then perform an update (via a delete then insert) upon the ADDITIONAL fields in a table ... where it finds a value match on either a unique index or a primary key.

The better/lower overhead way to do what you're talking about here is like so:

(edit, sorry did this wrong the first time)
delete from tbl1 where not exists (select * from tbl2 where tbl1.id = tbl2.id);
replace into tbl1 select * from tbl2;

Not only is it less 'work' but it also makes it so that there's no 'time' when your table is sitting there empty (which may or may not be good/matter, but is 'typically preferable').

here's an example to show how replace into is meant to work (copy and run in sql editor):

create table tbl1 (id int, value int, constraint pkbl1 primary key (id));
create table tbl2 (id int, value int, constraint pkbl2 primary key (id));

insert tbl1 select 1, 1;
insert tbl1 select 2, 2;

insert tbl2 select 1, 1;
insert tbl2 select 2, 99;
insert tbl2 select 3, 3;

replace into tbl1 select * from tbl2;

select * from tbl1;

drop table tbl1;
drop table tbl2;

/* you should get back a scenario where tbl1 matches tbl2:
1,1 (matching row on primary key with same value ... left alone ... nifty feature #1 of replace into)
2,99 (matching row on primary key with different value ... in this case, any other field's values get updated with new data ... nifty feature #2 of replace into)
3,3 (this was unmatched on primary key and hence a new row is inserted ... in your present scenario, all your rows are new ... so your replace is really just an insert)

which is 1/2 of what you wanted, but replace into will not DELETE 'missing' rows from the other table, it only inserts/updates.
*/

Here's the same example, plugging in the code I gave you, with an additional row in tbl1 that you no longer want. Note you get the same result in the end as the first example ... because you've first deleted the matching rows.

create table tbl1 (id int, value int, constraint pkbl1 primary key (id));
create table tbl2 (id int, value int, constraint pkbl2 primary key (id));

insert tbl1 select 1, 1;
insert tbl1 select 2, 2;
insert tbl1 select 4,99;

insert tbl2 select 1, 1;
insert tbl2 select 2, 99;
insert tbl2 select 3, 3;

delete from tbl1 where not exists (select * from tbl2 where tbl1.id = tbl2.id);
replace into tbl1 select * from tbl2;

select * from tbl1;

drop table tbl1;
drop table tbl2;

 

[Easy Rhino]

Because I couldn't find another way to update a table correctly. Everything else didn't remove entries that didn't exist in the table I was pulling from.

BEGIN
START TRANSACTION;
DELETE FROM services.corproster;
REPLACE INTO services.corproster (name)
SELECT name FROM seat.corporation_member_tracking;
COMMIT;
END

works

Aquinas is right. What you want is a view. You want to keep your production table pure and then you build a view off that table of the data you want to see. Creating a view is just as simple as creating a table if you are not using any logic with the view.

 

[Aquinus]

Aquinas is right. What you want is a view. You want to keep your production table pure and then you build a view off that table of the data you want to see. Creating a view is just as simple as creating a table if you are not using any logic with the view.

It will also always contains the values that are in the target table that the OP is copying from since a view is just a query that behaves like a table (see sub-query.) It eliminates maintaining consistent state on some table and it eliminates duplication of data. This doesn't work when you plan on storing extra data than just a name, but without more info about the problem being solved, any recommendation is probably going to be a bad one (shot in the dark.) Once again though, @ShiBDiB hasn't really said what he's trying to do, just how he's trying to do it. So it's hard to say what would be best.

Distraction: Personally, I like PostgreSQL because you can do cool things like make "INSTEAD OF" triggers on VIEWs, so you can insert, update, and delete on a VIEW when in reality, something else is happening. I do this kind of thing where output is typically denormalized but the way it is going to be stored is highly normalized (think logs with ip addresses and agent strings.)

 

[ShiBDiB]

I actually need help with this again, for some reason wasn't getting notfications.

The previous working query

BEGIN
START TRANSACTION;
DELETE FROM services.corproster;
REPLACE INTO services.corproster (name)
SELECT name FROM seat.corporation_member_tracking;
COMMIT;
END

no longer works now that I updated to mariaDB.

The view option seems nice, not sure if it'd work tho.

What this table is for is restricting access to a services page. Only users who's names are on the seat.corporation_member_tracking table in the column "name" are able to create accounts. I then have a second event that removes already present accounts who don't meet this criteria.

Now that I wrote it out I'm not sure why I don't just query the seat.corporation_member_tracking table in my check.

 

[suraswami]

Its been a while since I wrote any advanced sql code, here is the statement if it was M$ SQL Server

MERGE services.corproster corp
USING (SELECT name FROM seat.corporation_member_tracking)
AS JoinResult
ON (corp.name = JoinResult.name)
WHEN MATCHED AND ISNULL(corp.name, '') <> ISNULL(JoinResult.name, '')
THEN UPDATE SET corp.name = JoinResult.name
WHEN NOT MATCHED THEN
INSERT name VALUES JoinResult.name
;

OR may be try this

BEGIN TRANSACTION
UPDATE corp
set corp.name = source.name
from services.corproster corp join seat.corporation_member_tracking source
ON corp.name = source.name
INSERT INTO services.corproster (name)
SELECT name FROM seat.corporation_member_tracking
where name not in (select name from services.corproster)
COMMIT TRANSACTION

I would only delete and insert if the table has less rows

 

[Easy Rhino]

Now that I wrote it out I'm not sure why I don't just query the seat.corporation_member_tracking table in my check.

This is what I was wondering all along.