spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Inputing column values in one table from another related table


You can try this:

Update  ROOT.ISB_COURSE_REGISTRATIONS
set ROOT.ISB_COURSE_REGISTRATIONS.degree=
(
    Select MAX(ROOT.ISB_STUDENTS.degree)
    from ROOT.ISB_STUDENTS
    where
ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID
)

where exists 
(
    select 1
    from ROOT.ISB_STUDENTS
    where
ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID
 );

This correlated subquery is calculated for each row in ISB_COURSE_REGISTRATIONS

Select ROOT.ISB_STUDENTS.degree
from ROOT.ISB_STUDENTS
where
ROOT.ISB_STUDENTS.STUDENT_ID=ROOT.ISB_COURSE_REGISTRATIONS.STUDENT_ID

So if it returns several rows for a certain ISB_COURSE_REGISTRATIONS.degree it's not clear which one to set as a value.

Applying MAX(ROOT.ISB_STUDENTS.degree) to the all rows of this subquery guarantees there will only one row.

But you should be careful with this update. Maybe there are different courses a student attended and mybe there different degrees for each course.


Categories : SQL

Related to : Inputing column values in one table from another related table
Codeigniter - MySql : Join three column values of one table with the ID of another table
You do not need three joins with the category table. Instead join with all three fields as an accepted match: select a.id, a.artist_fname, a.artist_lname, b.artist_id, b.sm_file_name, b.category_id, b.category_id2, b.category_id3, c.id, c.category from ap_mini_artist a left join ap_mini_artwork b on b.artist_id = a.id left join ap_art_categories c on c.id in (b.category_id, b.category_id2, b.cate

Categories : PHP
MySQL: fill table column from other column with pivot table
I would go with a query like this: UPDATE tags t SET t.lid = ( SELECT q.lid FROM questions q JOIN q_t ON (q.qid = q_t.qid) WHERE q_t.tid = t.tid LIMIT 1 ) It might not be the best performance-wise, but for a one-time job, it should do. As an alternative: UPDATE tags t JOIN ( SELECT q.lid, q_t.tid FROM questions q JOIN q_t USING (qid) GROUP BY q_t.tid ) d USING(tid) SET t.lid

Categories : Mysql
Column name or number of supplied values does not match table
Here: INSERT INTO Comments VALUES ('Mdbuzzer','Times Up', 'Best post I have seen in a while', '22:55pm', '22:00pm'); You are inserting 5 values into a table with 4 columns. Sometimes using the more verbose syntax can help both with organization and error messages: INSERT INTO Comments(UsernameID, PostTitle, Comments, TimeofComments) VALUES('Mdbuzzer','Times Up', 'Best post I have seen in a wh

Categories : Sql Server
Creating a new table in a new worksheet with maximum column values from other worksheets
If all the worksheet names has the same format i.e XXX_XXX_XX_XXXX then it is pretty simple to extract those values. You can use Split function. here is an example Sub Sample() Dim sName(1 To 4) As String Dim i As Long sName(1) = "HP5_1gt_60_2010" sName(2) = "HP5_1gt_70_2010" sName(3) = "HP5_1gt_100_2008" sName(4) = "HP5_1gt_110_2008" For i = 1 To 4 Debug.Pri

Categories : Excel
Extract values from a specific column of an html table using bash
Try the below awk command, awk 'NR>1{print $3}' file This prints the value of third column except the one in the header. Update: awk -v RS='</tr>' -v F='<td>' '{$3=gsub(/<[^<>]*>/,"",$3);print $3}' file

Categories : HTML
Recently Add
SQL command to insert two primary keys into third table
How to do a registerform by ASP.NET VB SQL
Shortest time and trip between two stops across many trip options (GTFS)
Using IN and EXISTS subquery with AND operation instead of OR?
How to select multiple alias columns from the same table
Delete records from a given table
date manipulation in postgresql
PSQL query with join query from a db newbie
Protect LocalDB from user access
Update field with combined contents from other fields
SQL select unique records under certain priority and conditions from multiple columns
Multiply quantities for all parent child relationships
I'm having an issue running a query from a batch file
Concatenate strings while filtering by another column in SQL Server view
Parsing Dynamic XML to SQL Server tables with Parent and child relation
Using MERGE for delete and insert in oracle
how to display the pre value and post value of a column after updating it?
sql loop to fill up date between two time stamps
With SQL/TSQL, how can I use STUFF within this WHILE clause?
Challenging db table and query
Query to fetch row corresponding to Max value in SQL
Oracle SQL - ORA-00936 on DATE()
how to split single row into multiple row in db2?
Combine fields into one column
How can I perform an SQl where query then list properly?
SQL - Selection of the oldest order for every customer (join two tables)
Sum two counts in a new column without repeating the code
Creating multiple sum fields based upon criteria in another field in MS access 2007
Grouping by two values in same table
MS SQL last record
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.