gamestors
asked on
SLQ Update
Hi,
I have two tables and I want to update a field of want table based on the presence of the ID's in the other table.
For example:
Table A has the following records:
col1 col2
ID1 someData
ID2 someData
ID3 someData
Table B has the following records:
col1 col2
ID0 NULL
ID1 NULL
ID2 NULL
ID3 NULL
ID4 NULL
I want to update col2 in table B with 'Y' based on the occurence of the ID's in table A. In this case, col2 of ID1,ID2, and ID3 in table B should be marked with 'Y' after the update.
How do I do this with a single SQL update statement?
Thanks.
Gamestors
I have two tables and I want to update a field of want table based on the presence of the ID's in the other table.
For example:
Table A has the following records:
col1 col2
ID1 someData
ID2 someData
ID3 someData
Table B has the following records:
col1 col2
ID0 NULL
ID1 NULL
ID2 NULL
ID3 NULL
ID4 NULL
I want to update col2 in table B with 'Y' based on the occurence of the ID's in table A. In this case, col2 of ID1,ID2, and ID3 in table B should be marked with 'Y' after the update.
How do I do this with a single SQL update statement?
Thanks.
Gamestors
That's an sql server extension
I think this is ansi standard sql (could be wrong though).
update TableB
set col2 = 'Y'
where exists (select * from TableA where TableA.col1 = TableB.col1)
I think this is ansi standard sql (could be wrong though).
update TableB
set col2 = 'Y'
where exists (select * from TableA where TableA.col1 = TableB.col1)
ASKER
I should have mentioned that I use MySql.
I tried both but neither one worked.
I tried both but neither one worked.
gamestors
Try:
UPDATE tableB
set col2 = 'Y'
where col1 in (select col1 from tableA);
And yet another way may be:
UPDATE tableB INNER JOIN tableA
ON tableA.col1 = tableB.col1
SET tableB.col2 = 'Y';
A useful link for mySQL is www.mysql.com
Hope that helps & have fun!
Elaine
Try:
UPDATE tableB
set col2 = 'Y'
where col1 in (select col1 from tableA);
And yet another way may be:
UPDATE tableB INNER JOIN tableA
ON tableA.col1 = tableB.col1
SET tableB.col2 = 'Y';
A useful link for mySQL is www.mysql.com
Hope that helps & have fun!
Elaine
not sure about mysql..... but you can use cursors I imagine?
DECLARE
--declaration of cursor, selects column1 values from tablea, where ids match in tables
CURSOR c1 IS
SELECT b.column1
FROM tablea a,
tableb b
WHERE a.column1 = b.column1
-- creates a variable of same type of values selected in the cursor above (just 1 here, but could be many
-- columns)
c1_rec c1%ROWTYPE;
BEGIN
-- this opend the cursor, and loops through it until there is no more data returned from the cursor
-- same as using OPEN c1; FETCH c1 INTO c1_rec, EXIT WHEN c1%NOTFOUND
-- but shorter obviously.......... - works in Oracle anyway
FOR c1_rec IN c1 LOOP
UPDATE TableB
SET TableB. column2 = 'Y'
WHERE TableB.column1 = c1_rec.column1;
COMMIT;
END LOOP
END;
-- Hope that helps..... and these lines are comments by the way.........
-- ~ Liberator
DECLARE
--declaration of cursor, selects column1 values from tablea, where ids match in tables
CURSOR c1 IS
SELECT b.column1
FROM tablea a,
tableb b
WHERE a.column1 = b.column1
-- creates a variable of same type of values selected in the cursor above (just 1 here, but could be many
-- columns)
c1_rec c1%ROWTYPE;
BEGIN
-- this opend the cursor, and loops through it until there is no more data returned from the cursor
-- same as using OPEN c1; FETCH c1 INTO c1_rec, EXIT WHEN c1%NOTFOUND
-- but shorter obviously.......... - works in Oracle anyway
FOR c1_rec IN c1 LOOP
UPDATE TableB
SET TableB. column2 = 'Y'
WHERE TableB.column1 = c1_rec.column1;
COMMIT;
END LOOP
END;
-- Hope that helps..... and these lines are comments by the way.........
-- ~ Liberator
ASKER
Elainec,
According to the documentation on mysql.com, keywords 'IN', 'NOT IN',and 'NOT EXISTS' are not yet implemented in MySql.
Thanks anyway,
According to the documentation on mysql.com, keywords 'IN', 'NOT IN',and 'NOT EXISTS' are not yet implemented in MySql.
Thanks anyway,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was looking for a simple way to do the update. Your approach is too complicated. Thanks anyway.
i do not agree with the grade C you gave, gamestors.
i asked CS to correct this, because the approach isn't complicated at all, it is the way it works in SQL Server.
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20179769
Regards
i asked CS to correct this, because the approach isn't complicated at all, it is the way it works in SQL Server.
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20179769
Regards
I agree with the grade change. We are currently changing our grading system, and any comment that leads to an answer deserves an A.
Thanks,
ComTech
Community Support
Thanks,
ComTech
Community Support
MSSQL Server's Version:
UPDATE TableB
SET col2 = 'Y'
FROM TableB
JOIN TableA
ON TableA.col1 = TableB.col1
Cheers