VB and VBA Users Source Code: Using SQL to insert/update data in tables using multiple sub-selects
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Using SQL to insert/update data in tables using multiple sub-selects
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Tuesday, January 14, 2003
Hits:
1078
Category:
Database/SQL/ADO
Article:
I have frequently seen developers struggle to do multiple sub-select queries in updates and inserts. Below is a simple example which shows how to do two sub-select queries in an insert. --Create a table of users CREATE TABLE #users ( first_name varchar(50) NULL, last_name varchar(50) NULL, date_of_birth datetime NULL, instruction1 varchar(75) NULL, instruction2 varchar(75) NULL ) --Create a table of management divisions CREATE TABLE #lkp_management ( division int NULL, instructions varchar(50) NULL ) --Insert some instructions to our users, depending on which divisions they are in insert #lkp_management values (1, "Go home, ur fired") insert #lkp_management values (2, "Feel lucky, na - ur fired") insert #lkp_management values (3, "DOT COM eh, sounds like a great idea") insert #lkp_management values (4, "You've been restructured... close the door on your way out.") --Now add a user and give him two instructions from the #lkp_management table. INSERT #users ( first_name, last_name, date_of_birth, instruction1, instruction2 ) --Start you insert list with a select select first_name = "Andrew", last_name = "Baker", date_of_birth = "29 oct 1900", --Now add your two sub-selects instruction1 = (SELECT instructions FROM #lkp_management WHERE division = 1), instruction2 = (SELECT instructions FROM #lkp_management WHERE division = 2) --Return your results select * from #users --Drop your tables drop table #users drop table #lkp_management
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
