Category: Databases

Looking for directions. Where would one go to find a downloadable excel sheet, listing the streets of Algeria city?

I have a table with the following fields:

CREATE TABLE text (
    drawing INT NOT NULL,
    blockID INT NOT NULL,
    entityID INT NOT NULL,
    style INT,
    txt VARCHAR(255) NOT NULL,
    attrib INT);

My csv file contains the data:

19  1CB2    E49 2   CLIENT MODULAR  1C2A
19  1CB3    E4B 2   CLIENT UG - 2 MODULAR PILOT PLANT   1C2C
19  1CB4    E4C 2   100 - 500 MICRON    1C2D
19  -1  E50 2   USERNAME    1C31
19  1CBA    E51 2   15.8.2020   1C32
19  1C16    E58 2   PLANT   1C39

I'm using the following SQL to import the CSV file:

LOAD DATA INFILE '/tmp/_P2.8Q9nJ4_/text' INTO TABLE text (drawing,@blockID,@entityID,style,txt,@attrib) SET blockID=UNHEX(@blockID),entityID=UNHEX(@entityID),attrib=UNHEX(@attrib);

But this is the result:

+---------+---------+----------+-------+-------------------------------------------+--------+
| drawing | blockID | entityID | style | txt                                       | attrib |
+---------+---------+----------+-------+-------------------------------------------+--------+
|      19 |       0 |        0 |     0 | CLIENT MODULAR                            |      0 |
|      19 |       0 |        0 |     0 | CLIENT UG - 2 MODULAR PILOT PLANT         |      0 |
|      19 |       0 |        0 |     2 | 100 - 500 MICRON                          |      0 |
|      19 |       0 |        0 |     2 | USERNAME                                  |      0 |
|      19 |       0 |        0 |     2 | PLANT                                     |      0 |
|      19 |       0 |        0 |     2 | 15.8.2020                                 |      0 |

What is the correct way to import a CSV file into my table?

I have the following query which has died after running over an hour on my local mysql 8 server:

UPDATE download
  LEFT JOIN lookup ON download.ip_address between lookup.start_ip AND lookup.end_ip
SET ref = (
    SELECT lookup.id FROM lookup WHERE download.ip_address between lookup.start_ip AND lookup.end_ip);

All ip fields are IPV4 and stored as unsigned integers and are indexed. The lookup table is approx. 3M rows, relating ip ranges to country, area, and city. The download table is approx. 2K rows. What I'm trying to do is get the id from the lookup table row that has the ip range that the download ip_address falls into.

Does someone see a problem with the query?

Hi there,
Not sure if I am in the right place, but I wanted to ask where I can access a csv file for all the streets in Belgium?

Hi all, I have a question about updating two values in a sql table using a subquery. My database is called books and the below table books too:

id   title    author   location_id
11  Tera    Netrear 4
14  Disser  Bubu    1
15  Tenno   Puoio   4
16  Oban    Tyrone  5
18  Sisnobi Teru    3

Location_id is indeed another table as it's a foreign key in this table but that shouldn't matter. So what I want to do is to change all the location_id currently at 4 to 2. I know i could do it like this
update books set location_id = 1 where location_id = 4 but I'd like to know if I could do it with a subquery, something like update (SELECT * from books where location_id = 4) as thebook set thebook.location_id = 2 but when I run this query it tells me that the target table thebook UPDATE is not updatable. Why is that and can this be achieved - I don't really mind if it's not performant etc, I think it should be possible but I can't quite get the right way to do it...
So, the idea is, make a selection first then change a/more value/s on that selection, this is the concept I'm try to prove.
Any idea?

I'm trying to create a trigger that does two things, first take an ip that's in dot notation and run inet_aton on it and put the result in another field. Second, checks a lookup table to identify an ip range that the result of the first action falls into and enters the id of that row in the table. Do I have to break this down to separate triggers, a before insert and an after insert? This is a mysql database.

DELIMITER //
CREATE TRIGGER download_ins  BEFORE INSERT ON download 
 FOR EACH ROW begin
   declare vip varbinary(16);
   select `ADDRESS` from `download`;
   vip = inet_aton(`ADDRESS`);
   set NEW.ip_address = vip;

   declare vrefer int(25) unsigned;
   select id
  from `ip_lookup`
  where NEW.ip_address between ip_lookup.start and ip_lookup.end
  limit 1; 
  if vrefer is not null then
    set NEW.refer = vrefer;
  else
    exit;
  end if;
END; //

DELIMITER ;

Thanks in advance

Hi all,
Before I begin this question, and before you tell me we dont do homework, I would like to state that I have read a few articles, I have tried a couple of functions (=REPLACE, =SUBSTITUTE) but i'm not able to get the reuslts require, I'm new to Excel
Question: Useappropriate text functions to shorten the variable names to something like Arizona Females Young, Arizona Females Old, and Arizona Females All, also is there a way to do it automatically for all variables in 1 function. The screenshot is attached.
It is kind of urgent (I understand people help within their own time).
Thanks in advance.

My code has a query that looks like:

INSERT INTO table
VALUES (...)
ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    ...

id is a PRIMARY KEY.

My question is that I can't for anything remember why I have id = LAST_INSERT_ID(id). I wrote it years ago and it's not commented. Can anyone think of a reason why I was doing this?

Hello all.
I have a funtion in MYSQL to substruct days from a date.
This function seamsto work but some dates it times out.
This is my function

CREATE DEFINER=`root`@`localhost` FUNCTION `Calculater`(intrval int, req date) RETURNS date
    DETERMINISTIC
BEGIN
declare calculated date;
declare added int default  0;

Lbl_Loop: Loop
IF added=intrval then
leave lbl_loop;
End IF;
set calculated = DATE_SUB(req,interval 1 day);
IF weekday(calculated)<5  then
set added= added+1;
End If;
end loop;
RETURN calculated;
END

If I do select Calculater(1,"2020-02-04"); it works, but select Calculater(1,"2020-02-02"); it runs for a few seconds and My SQL times out.

Thanks in advance.

Sometimes you need to delete duplicate rows in a database :)

In the future, set UNIQUE keys when you need them :)

In the example below, we have a table appropriately named TABLE_NAME and there are multiple rows that have the same value for the duplicate_field field. In this example, we want to remove the ones where the id field is set to "remove". In other examples, you can pick any conditional you want when choosing which of the rows to remove or which to keep, including conditionals between them (e.g. foo.id < bar.id will keep the row with the highest id value and delete the others).

Alternatively, if you do want to add that UNIQUE index, you can do the following to brute force dropping duplicate rows:

ALTER IGNORE TABLE TABLE_NAME
ADD UNIQUE INDEX index_name (duplicate_field);

I come across this problem every so often and end up having to do work arounds when I would prefer to keep it in the database. I have a query where the data in the query updates frequently so the query won't stay in the cache but I don't need the query to be accurate. I was trying to avoid adding an extra step of storing the data in a temp table and having to update it on a timer.

Does anyone know of a way to get MariaDB or MySQL to Keep the result set for a period of an hour or so before actually running the query again, I have an example of a query below which takes between 4.8-6 seconds to run and the wait is too long and also is needless work for the server, It can just do the query once and get the 4 numbers - then all subsequent requests just return the 4 numbers and every 20 minutes or 1 hour the 4 numbers get updated.

Is there a built in funtion for this or is the only solution to make your own table? I could end up with many of these and I don't want to end up with 100's

SELECT
    SUM(CASE WHEN p.id IS NULL AND f.loc = 1 THEN 1 ELSE 0 END)/250 to_print
    , SUM(CASE WHEN p.id IS NULL AND f.loc NOT IN(1,2,8) THEN 1 ELSE 0 END)/250 to_print_out
    , SUM(CASE WHEN p.id IS NOT NULL AND p.print_date BETWEEN '2020-01-09 14:00:00' AND '2020-01-16 14:00:00' THEN 1 ELSE 0 END)/250 printed_tw
    , SUM(CASE WHEN p.id IS NOT NULL THEN 1 ELSE 0 END)/250 printed
    FROM typist t
    LEFT JOIN printing p ON t.typed_id = p.print_id AND p.type = 4
    LEFT JOIN files f ON t.file_id = f.file_id
    WHERE
    t.type = 1
    AND f.file_id IS NOT NULL
    AND NOT EXISTS(SELECT DISTINCT l.id FROM letters l WHERE l.deleted = 1 AND t.letter_id = l.id)

This is the EXPLAIN for the query

"1" "PRIMARY"   "t" "ref"   "file_id,letter_type,print_stat"    "print_stat"    "2" "const" "134243"    "Using where; Using index"
"1" "PRIMARY"   "f" "eq_ref"    "PRIMARY"   "PRIMARY"   "3" "sys.t.file_id" "1" ""
"1" "PRIMARY"   "p" "ref"   "id,type"   "particle_id"   "8" "sys.t.typed_id"    "1" "Using where"
"2" "MATERIALIZED"  "l" "ref"   "PRIMARY,deleted,deleted_file_id"   "deleted"   "1" "const" "4741"  "Using index"

I have already had numerous variations to try optimising this and the above is the best I have got so far. t has around 200,000 rows, l has around 300,000, f has about 130,000, p has around 300,000 but will bloat well over 1,000,000 in time.