I have a table that is populated with a USERID, CLIENTID and PROJECTID. One client can have multiple projects and so for each project a new entry has to be put in for a single user. For example:
What I'm trying to do is for each distinct project to add a new userID to it. So for example I want to add USERID ZXY to every project. So first I identify all the unique PROJECTID's
SELECT DISTINCT PROJECTID FROM TABLE
PROJECTID 000001 000002 000034
What I need help on is how I use these values and INSERT INTO TABLE all these entries again with the USERID ZXY. So that my table looks like this:
INSERT tbl(userid, clientid, projectid) SELECT DISTINCT 'ZXY', a.clientid, a.projectid FROM tbl a WHERE NOT EXISTS (SELECT * FROM tbl b WHERE b.userid = 'XYZ' AND b.clientid = a.clientid AND b.projectid = b.projectid)
quote:Originally posted by sunitabeck
Would this work for you?
INSERT INTO YourTable
SELECT
'ZXY',MAX(CLIENTID),PROJECTID
FROM
YourTable
GROUP BY
PROJECTID;
INSERTING VALUES BASED ON DISTINCT FIELDS
Posted - 10/09/2012 : 04:04:49
