• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Inserting record

Currently I have a table named timetable (jobid, day) and having values of:
J001 Wed
J002 Thu
J002 Fri
:
:
J199 Mon.

I want to insert a Sat value for all jobid found in the current table. Please advice me on the SQL statement I should construct to achieve this purpose.

TIA.
0
bpyeo
Asked:
bpyeo
  • 2
1 Solution
 
brejkCommented:
INSERT INTO timetable (jobid, day)
SELECT DISTINCT jobid, 'Sat'
FROM timetable
0
 
brejkCommented:
And if there are some Saturdays in your table then:

INSERT INTO timetable (jobid, day)
SELECT DISTINCT t.jobid, 'Sat'
FROM timetable t WHERE NOT EXISTS(
  SELECT 1 FROM timetable WHERE day = 'Sat' AND jobid = t.jobid
)
0
 
dportasCommented:
You could also do it using MERGE (new feature in 2008). Do you have a Jobs table? If so, try:

MERGE INTO timetable
USING jobs
 ON timetable.jobid = jobs.jobid
  AND timetable.day = 'Sat'
WHEN NOT MATCHED THEN
 INSERT (jobid, day) VALUES (jobid, 'Sat');

If not, you'll have to substitute the derived table (SELECT DISTINCT jobid FROM timetable) in place of Jobs.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now