Write update query using subquery in MSSQL MYSQL

Everybody knows about SQL update Query.

Even its way easy to update some columns based on some condition.

Suppose you are in a condition where,

  • You want to update a column be fetching or retrieving data from another table.
  • You want to update 1 column by retrieving data from same table based on some condition.
  • You want to update 1 column by retrieving data from other table based on some condition.

Then all these Question’s answer is Write update query using sub query

1
2
3
4
5
6
7
8
9
UPDATE tableName
SET updateColumnName = (
                 SELECT top 1 column2 FROM otherTableName otherTableAlias
                 WHERE
                     otherTableAlias.column1 = tableName.column1
                 AND
                     otherTableAlias.column2 = tableName.column2
                 )
WHERE tableName.is_exit ='Y' AND tableName.project_manager_id = 192

Example of the update query using sub query by getting data from other table

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE  main_beneficiaries
SET exit_date = (
                 SELECT top 1 paid_date FROM main_beni_inst_masters main_beni_inst_masters
                 WHERE
                    main_beni_inst_masters.beni_id = main_beneficiaries.ben_id
                 AND
                    main_beni_inst_masters.project_manager_id = main_beneficiaries.project_manager_id
                 AND
                    main_beni_inst_masters.is_paid = 'Y'
                )
WHERE is_exit ='Y' AND exit_date IS NULL
AND project_manager_id = 192

Example of the update query using sub query by getting data from same table

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE  main_beneficiaries
SET exit_date = (
                SELECT top 1 paid_date FROM main_beneficiaries main_beneficiaries2
                WHERE
                     main_beneficiaries2.beni_id = main_beneficiaries.ben_id
                AND
                    main_beneficiaries2.project_manager_id = main_beneficiaries.project_manager_id
                AND
                   main_beneficiaries2.is_paid = 'Y'
)
WHERE is_exit ='Y' AND exit_date IS NULL
AND project_manager_id = 192

 

I have written all there query for MSSQL, in the mean time those who are familar with MYSQL, they have to use LIMIT in place of  TOP 1.

Because sub query should return 1 record. so for limiting no. of records , we have to use LIMIT 0,1 for MYSQL and TOP 1 for MSSQL.

Keep Coding :)