Updating SQL Query on multiple values

Question

I have two models one is student_profile where I have university field to show university name. I have a list of universities where I need to update another table called Package only if one of the university exists in table. Table has 1000 records and I need to update all the entries with one query.

If university a, b, c, d exists in student_profile.
Update few "Package" table fields.

My tables:

 +---------------------------+
 | student_profile           |
 +---------------------------+
 | id         | int(11)      |
 | first_name | varchar(45)  |
 | last_name  | varchar(45)  | 
 | university | varchar(45)  | 
 +---------------------------+

 +---------------------------+
 | package                   |
 +---------------------------+
 | student_id  | int(11)     |
 | is_active   | tinyint     |
 | package_type| varchar(45) | 
 +---------------------------+

ForeignKeys in StudentProfile Table:

name = student_package
schema = mydb
Column = student_id
reference Schema = mydb
referenced table = student_profile
referenced column= id

If university exists I need to set is_active=True and set package.student_id as student_profile.id and package.package_type as 'UniverityEnrolled'.

So why leave that bit of important info out of your question? — Sloan Thrasher, Apr 10 '17 at 18:22
I added university examples above like a, b c, d etc. Those are simulated university names. — Anonymous Coder, Apr 10 '17 at 18:23
Which DBMS are you actually using? mysql <> sql server. Also, here is a great to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ Following the advice there will greatly improved your question. — Sean Lange, Apr 10 '17 at 18:24
@AnonymousCoder but are they in a SQL Table or some loose list somewhere? If they're in a table, they'll be easier to work with and you'll get a better answer. — EMUEVIL, Apr 10 '17 at 18:24
University field is in table and there are universities exist like a, b, c. All I need to achieve is to update Package on certain universities such as if "a" exists in university it should update Package table. — Anonymous Coder, Apr 10 '17 at 19:05

score 0 · Answer 1 · edited Apr 10 '17 at 18:32

0

Based on what I understand of the question, this may be your solution:

UPDATE package 
    SET is_active = 1,package_type = "UniversityEnrolled"
WHERE student_id IN 
(SELECT id FROM student_profile WHERE university IN ("a","b","c","d"))

edited Apr 10 '17 at 18:32

Palindromer

854
1
10
29

answered Apr 10 '17 at 18:26

dev8080

3,950
1
12
18

I got this message @dev8080. "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.455 sec" – Anonymous Coder Apr 10 '17 at 18:48
This is because though I amusing student_id in where clause that is not the primary key. See http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench. – dev8080 Apr 10 '17 at 19:07

score 0 · Answer 2 · answered Apr 10 '17 at 18:26

0

To figure something like this out, start with a SELECT that outputs the records to be updated.

Then when it is working, convert to an update statement.

SELECT *
FROM `StudentProfile` a
JOIN `Package` b
ON a.`id` = b.`student_id`
WHERE `university` in ('a','b','c');

UPDATE `StudentProfile` a
    SET `is_active` = 1
JOIN `Package` b
ON a.`id` = b.`student_id`
WHERE `university` in ('a','b','c');

answered Apr 10 '17 at 18:26

Sloan Thrasher

4,953
3
22
40

Workbench shows error on "JOIN 'Package' b under Update. – Anonymous Coder Apr 10 '17 at 18:39
Could be simple, Move the Set line to after the ON line. Not tested, just off the top of my head. – Sloan Thrasher Apr 10 '17 at 18:52
Sloan I think we are close but I am getting same error what I get from solution posted by dev8080. Error 1175 You are using safe update mode....etc. – Anonymous Coder Apr 10 '17 at 19:09
id and student_id should both be primary keys in their tables. Are they? – Sloan Thrasher Apr 10 '17 at 19:20

Updating SQL Query on multiple values

2 Answers2