Tag Info

New answers tagged

0

Another approach: LOAD DATA into a new (temp) table UPDATE real JOIN new ON ... SET real.x=... WHERE ... -- Update any existing rows that need updating INSERT INTO new SELECT new LEFT JOIN real ON ... WHERE real.id IS NULL -- Add any rows that do not already exist


2

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example: INSERT INTO Contacts (number, ...


0

Here's a model that I think would work for you problem. For assignments that take place for all week days, you can create one row in AssignmentDay per WeekDay for that Assignment. I'll create an example scenario and sample data to explain: Jim (idUser=1) is working on the American Express project (idProject=1) every day between Jan 1st and Feb 15th, ...


0

All of this could have been accomplished with the INFORMATION_SCHEMA database The table names are in the INFORMATION_SCHEMA SELECT table_name FROM information_schema.tables WHERE table_schema='conntrack'; You could then line up the tables that exist against the tabidx table SELECT B.table_name FROM (SELECT date FROM tabidx) A INNER JOIN (SELECT ...


0

Google "open source project management software" - I got 49 million hits. There appear to be interesting options here, here and here. There is very little that is truly original in software. What I would advise you to do is to download (a few of the) projects which look interesting, then check out their data models - that's the beauty of Open Source - look ...


1

The MySQL SET datatype is a disaster - as are any type of array datatypes (supported unfortunately by many RDBMSs). Check out my answer to another question here. As I note, MySQLs SET is a breach of Codd's second rule - no repeating group datatypes. It is also completely non-portable should you wish to change RDBMS. If you wish to store that data this way ...


0

Store all products in one table and put a "parent_id" (or something like that) field into that table that will contain a reference to the parent product. So, for primary products parent_id is null, and for secondary products, parent_id containts id of their primary product. In all other places you reference your products table, and, if you decide some day ...


2

Something like this would give you the total across all three of those days: SELECT COUNT(attendance.AttendanceID) as 'Total Attendance' FROM student INNER JOIN attendance ON attendance.StudentID = student.StudentID AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015') WHERE student.WorkshopID = '101' If you wanted the total separately for ...


0

Is this what you have in mind? Note the replacement of "$afdasfd" with "afdasfd" in the last line of the table. MySQL's string functions can be found here. mysql> select * from code; +------+-----------------+---------+ | id | xyz | value | +------+-----------------+---------+ | 12 | ATHHYIII | 152.63 | | 13 | KKKIP ...



Top 50 recent answers are included