56402

Query to Update Table using another table as information?

I have two tables, Staff and Wages

Staff Contains

id, name, jobID, wage 1 Name1 2 2 Name2 4 3 Name3 1 4 Name4 2

Wages Contains

JobID, Wage 1 1500 2 800 3 1600 4 2000

(There are alot more columns in the actual one I have just took the top 4)

I am missing the wages inside the Staff table, and the wages I need in the staff table are the rates in the Wages table..

So I need a query which would make the Staff table look like:

id, name, jobID, wage 1 Name1 2 800 2 Name2 4 2000 3 Name3 1 1500 4 Name4 2 800

An example Query which I tried was:

UPDATE `Staff` SET wage = (SELECT wage FROM `Wages`) WHERE jobID = (Select jobId FROM `Wages`)

Thanks.

Answer1:

I would just leave the tables as they are (without the wage column in Staff), in their normalized state, and run this query anytime I need the full set of (denormalized) data:

SELECT s.id, s.name, s.jobID, w.wage FROM Staff s LEFT OUTER JOIN Wages w ON s.jobID = w.jobID

Answer2:

In MySQL,

UPDATE Staff a INNER JOIN Wages b ON a.jobID = b.JobID SET a.wage = b.wage

In MSSQL,

UPDATE a SET a.wage = b.wage FROM Staff a INNER JOIN Wages b ON a.jobID = b.JobID

Recommend

  • Cash breakdown spreadsheet: possible limited precision? [closed]
  • HTTP Error 404.0 Not Found when export from crystal report
  • Loop/Iterate through date range while inserting
  • R, command line, write to file fails
  • how to center matlab .eps figure in latex document
  • how to edit project settings in xcode 4.2?
  • CSS transition glitch
  • Basic Authentication over redirection
  • Shadows for Instantiated Game Object bug?
  • Cannot page through all results using nextPageToken on YouTube search API v3
  • unexpected behaviour of Google BigQuery WHERE NOT list CONTAINS string
  • Extract All Possible Paths from Expression-Tree and evaluate them to hold TRUE
  • Cannot get text from text area
  • MySQL performance when updating row with FK
  • NHibernate manually control fetching
  • How to 'create temp table as select' in Slick?
  • How to get data from **Realm database** using **date object**?
  • Do query loads all the data in memory
  • Salesforce Different WSDL files and when to use
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Merging rows to columns
  • Breaking out column by groups in Pandas
  • presentShareDialogWithParams posts to FB wall, but callback handler results say error
  • Unable to get column index with table.getColumn method using custom table Model
  • Zurb Foundation _global.scss meta styles for js?
  • Time complexity of a program which involves multiple variables
  • ActiveRecord query for a count of new users by day
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Checking free space on FTP server
  • Change multiple background-images with jQuery
  • Algorithm for a smudge tool?
  • Can Jackson SerializationFeature be overridden per field or class?
  • Display Images one by one with next and previous functionality
  • Android Studio and gradle
  • SQL merge duplicate rows and join values that are different
  • Why joiner is not used after Sequence generator or Update statergy
  • Exception on Android 4.0 `android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode)`
  • File not found error Google Drive API
  • Is it possible to post an object from jquery to bottle.py?